Forum Discussion
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' ...
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".
= let
cols = Table.ToColumns(your_tab), /*{"id","v1","v2","v3"}*/
set=List.Accumulate(lpo,List.Zip({cols{1},cols{2},cols{3}}),(s,c)=> List.ReplaceRange(s,c,1,{{0,0,0}}))
in
Table.FromColumns({cols{0}}&List.Zip(set), {"id","v1","v2","v3"})Perhaps for transforming large data frames Power Query is not the most suitable tool.
You should consider using other languages (JULIA's DataFrames.jl package for example solves the whole thing (100 million lines) in less than 10 sec with just one half line instruction)