Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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"
@Anonymous I think I've seen @ImkeF and @edhans do this, I believe it involves feeding lists in using { } or something, can't remember.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.