Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Replacing multiple value at once

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

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

    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"

     

     

     

     

     

     

     

     

     

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.