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

vyacheslavg's avatar
vyacheslavg
Helper II
5 years ago

Yes! 

Wubba Lubba dub-dub!

 

This is absolutely what I meant.

 

I rewrote it slightly (extracted the logic to the separate function) - to be the same as I typically do in Pandas. It is easier to remember for me and easier to transfer and apply functions.

 

Very cool, I've learned a new stuff about Power query and now can copy Perl functions to M almost without rework(!).

 

 

 

 

let
    
    fnTest = (arg1, arg2) as record => 
        if arg1 <=1 
        then [newarg1 ="a", newarg2=arg2+1] 
        else [newarg1 ="b", newarg2=arg2+5],          
        
    
    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 fnTest([p1], [p3])),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"newarg1", "newarg2"}, {"Custom.newarg1", "Custom.newarg2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"p2", "p3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.newarg1", "p2"}, {"Custom.newarg2", "p3"}})
in
    #"Renamed Columns"