Forum Discussion

rishirajdeb's avatar
rishirajdeb
Icon for Advocate I rankAdvocate I
4 years ago
Solved

Conditionally transform multiple columns in single step

Hi All,   I need to implement the below requirement for a very large dataset (hence need a solution which would perform well). Some mock-up data: The requirement is when the status is 'AA' ...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0BBKGBkDC0hhImCjF6kQrGQFZTs4gMVMgYQZSZwqRAamJigIpBKmxsABpVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, status = _t, value1 = _t, value2 = _t, value3 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,each List.Contains({"AA","ZZ"},[status]),"0",(o,c,r)=> if c then r else o,{"value1","value2","value3"})
in
    #"Replaced Value"
    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
serpiva64's avatar
serpiva64
Icon for Solution Sage rankSolution Sage
4 years ago

Hi,

you can try this:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY+xDcAgDAR3oaYIJgQogUgMkA7E/mvEH4noC06PT9hmTuOMNaUo3EFYdhrRWJsiBwKM19iqInoCzKlxDMUpBBg8H5jjAwHm0tgf3P+DcsRiaOU8ASbtxcQRYPIen4QA833s7igkwlov", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, status = _t, Value1 = _t, Value2 = _t, Value3 = _t]),
#"Filtered Rows1" = Table.SelectRows(Source, each ([status] <> "AA" and [status] <> "ZZ")),
#"Filtered Rows" = Table.SelectRows(Source, each ([status] = "AA" or [status] = "ZZ")),
#"Multiplied Column" = Table.TransformColumns(#"Filtered Rows", {{"Value1", each Text.From( Number.FromText(_) * 0), type text},{"Value2", each Text.From( Number.FromText(_) * 0), type text},{"Value3", each Text.From( Number.FromText(_) * 0), type text}}),
#"Appended Query" = Table.Combine({#"Filtered Rows1", #"Multiplied Column"}),
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"id", Order.Ascending}})
in
#"Sorted Rows"

 and from this 

you get this

 

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !