Forum Discussion
From procedural language to Power query
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
Hi PhilipTreacy
Thanks, but unfortunately, I came up with almost the same solution.
The problem is - these _if-then-else_ conditions could be much more complicated (sometimes up to 30 conditions).
If I try to "decompose" or "unpiwot" conditions for each variable, it will be very, very time-consuming and hard to maintain in case of any change. It is not easy even for this very simplified example, but in a real-life situation it would be much harder.
Even the pseudo-code does not look nice (repetition...) 😞
if p1 = 1
then
p2 = "a" else p2 = "b"
if p1 = 1
then
p3 = p3+1 else p3 = p3+5
You could return a record, list, table, and/or function from a single if expression that has all the replacement values and/or values to add. However, transforming all the needed columns with those values will be tricky (but probably doable). My guess is that you would also not consider it elegant. I also recommend the standard approach.
Regards,
Pat