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.
Hello,
I need to replace values in power query based on a condition:
If Column B contains 31456 or 42987 or 45028 change Column A to "Direct sales" otherwise leave column A as is. I've tried every example of code I can find on here and none work.
Solved! Go to Solution.
Hi @amay,
if you want to actually replace rather then add column and then delete/rename (which I think just as equally Ok performance-wise), you can try this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcfLFYAgDATAXvbMARJQOPq3h7z034a6Zm5jhgUJWmqb4Mmwvqsy+sxt31qWzu2xwR3/NHNnrHBXTLg7pnB/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [B], "Direct Sales", (x, y, z)=> if List.Contains({31456, 42987, 45028}, y) then z else x,{"A"})
in
#"Replaced Value"
Cheers,
John
Hi @amay,
if you want to actually replace rather then add column and then delete/rename (which I think just as equally Ok performance-wise), you can try this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcfLFYAgDATAXvbMARJQOPq3h7z034a6Zm5jhgUJWmqb4Mmwvqsy+sxt31qWzu2xwR3/NHNnrHBXTLg7pnB/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [B], "Direct Sales", (x, y, z)=> if List.Contains({31456, 42987, 45028}, y) then z else x,{"A"})
in
#"Replaced Value"
Cheers,
John
you can try this. add a conditional column
refer two pics.
If I answered your question, please accept it as solution and give kudos.
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.