Replacing multiple value at once

[Anonymous]

Is there any way to replace multiple values in a column in just one step?
Let's say my column has a, b and c and I want them to be 1 2 and 3. I know I cant create a conditional column but can I make it with Table.ReplaceValue or any other function and avoid creating a new column

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may add a new step with following codes.

= Table.TransformColumns(#"Changed Type",{"Text",
each 
if _="a"
then 1
else if _="b"
then 2
else if _="c"
then 3
else null 
})

or

= Table.ReplaceValue(#"Changed Type",each [Text],each true,
(x,y,z)=>
if y="a" then 1
else if y="b" then 2
else if y="c" then 3
else null,
{"Text"}
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

there are many diffrent ways to accomplish that task.

Following of these, you could use (after a short adaptation) the schema proposed (but seems ultimately not adopted) here by @carlpaul153 .

you have to define  a list of words to be replaced wtbr={"undici","ventidue","trentatre"} and a list of worrds that replace the first ones wtrtfo={"11","22","33"}

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcyxDYAwDETRVU6p2ShKEWEXlsBBIUlByTQUbMEmTIIVAc019/S9d0VA3BIoZkZVklEwppWhSdFYi1BlFwaTFXPHgpLtiLa497Ofn8R1QCZQWhaxqLzJbt66iaoonLcO/pYL4QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

    wtbr={"undici","ventidue","trentatre"},
    wtrtfo={"11","22","33"},
    n=List.Count(wtrtfo),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Accumulate({0..n-1}, [Column1],(state, current) => Text.Replace(state, wtbr{current}, wtrtfo{current})))
in
    #"Added Custom"

[Greg_Deckler]

@Anonymous I think I've seen @ImkeF and @edhans do this, I believe it involves feeding lists in using { } or something, can't remember.