Text › Table.ReplaceValue, Replacer.ReplaceText12 / 47

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeTable.ReplaceValueReplacer.ReplaceText

Table.ReplaceValue: turn the +62 prefix into 0

Wulan sells bottled sambal through a network of resellers. Her WhatsApp broadcast app only accepts numbers starting with 0, but some numbers in the reseller data are written with the +62 prefix.

Task

In the WhatsApp column, replace the text +62 with 0 using Table.ReplaceValue and Replacer.ReplaceText. Example: "+6281234560011" becomes "081234560011". Numbers that already start with 0 stay the same. Columns and row order stay unchanged.

Data sourcesReseller

Required functionsTable.ReplaceValueReplacer.ReplaceText

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Run previewCtrlEnter
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
Table.ReplaceValue has 5 arguments: the table, the old value, the new value, how to replace, and the list of columns to process.
Hint 2
Replacer.ReplaceText replaces a piece of text inside a cell, while Replacer.ReplaceValue only replaces when the whole cell equals the old value.
Hint 3
Table.ReplaceValue(Source, "+62", ___, Replacer.ReplaceText, {___})

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

let
    Source = Reseller,
    #"Replaced Value" = Table.ReplaceValue(Source, "+62", "0", Replacer.ReplaceText, {"WhatsApp"})
in
    #"Replaced Value"

Explanation

Table.ReplaceValue(Source, "+62", "0", Replacer.ReplaceText, {"WhatsApp"}) looks for the piece "+62" in every cell of the WhatsApp column and replaces it with "0". With Replacer.ReplaceValue nothing would change, because no cell is exactly "+62".
Phone numbers must be stored as text: in a number column the leading 0 disappears. Note that Replacer.ReplaceText replaces every occurrence inside the cell. For messier data (say "62812..." without the plus sign), you need extra logic with Text.StartsWith.

Power Query· sources: Reseller

let
    Source = Reseller
in
    Source

Loading the Advanced Editor…

Edit the M code until the result matches, then press Check answer.

Validation result