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
Here is the full example how I did it in Pandas.
How it works on a high level:
- Import data from Excel into Pandas dataframe.
- Define function template.
- Apply function template to each row of dataframe; get a tuple as a result of function for each row.
- Split the tuple into columns and delete the original column.
My take on how this can be implemented in M
- Import data from Excel.
- Create a function template (if-then-else, what to return). Function must return record type. // Don't know how to do it. Previously worked only with simple functions, return = single value.
- Apply function template to data (create custom column) and calculate a record as a result of function.
- Expand the column with record into columns and delete the original column.
So it should very similar, I just don't have enough knowledge about M.
Could not googled any results.
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