Forum Discussion
From procedural language to Power query
- 5 years ago
Is this what you mean? It yields your desired results but not sure if it's in the way you are hoping for (but it does make a record dependent on the p1 value). To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUaoAYnOlWJ1oJSMgqxKILcA8kFwVEJsqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [p1 = _t, p2 = _t, p3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"p1", Int64.Type}, {"p2", type text}, {"p3", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [p1] <= 1 then [newP2 = "a", newP3 = [p3] + 1] else [newP2 = "b", newP3 = [p3] + 5]), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"newP2", "newP3"}, {"newP2", "newP3"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"p2", "p3"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"newP2", "p2"}, {"newP3", "p3"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"p2", type text}, {"p3", Int64.Type}}) in #"Changed Type1"Regards,
Pat
This can be done easily in the query editor, but will require an if .. then .. else for each column (w, y, z, etc.). It will work well and should be performant. Are you trying to do if differently as an academic exercise to better understand M? If so, I get it but not sure I want to go down this particular rabbit hole.
Regards,
Pat
Could you please provide an example?
https://d13ot9o61jdzpp.cloudfront.net/files/pbiforum/vyacheslavg.xlsx
Even if I need to copy existing logic over each column...
The major "wish" from my side - is that I don't want to change the logic, just copy/paste the logic from Perl. Even if I have to copy it several times (sigh).