Forum Discussion

vyacheslavg's avatar
vyacheslavg
Helper II
5 years ago
Solved

From procedural language to Power query

Please help with translation from procedural language logic (like Perl or Python) to Power Query. Background Sometimes I need to implement old Perl code via Power Query. Simple example in pseudoco...
Need Help
Tutorial Requests
  • mahoneypat's avatar
    mahoneypat
    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

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

Hi vyacheslavg 

Not quite the samein PQ.  You can ceate 2 Custom Columsn and implement the logic for one column at a time.  So you'd write an if then else for the p2 column,then another if then else for p3.

Sample Excel workbook here (Excel 365)

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"p1", Int64.Type}, {"p2", type text}, {"p3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "p2.2", each if [p1] = 1 then "a" else "b"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "p3.2", each if [p1] = 1 then [p3]+1 else [p3]+5),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"p2", "p3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"p2.2", "p2"}, {"p3.2", "p3"}})
in
    #"Renamed Columns"

 

After this you have 5 columns but you delete your original p2 and p3 leaving you with the desired result.

Regards

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.