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

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.

 

 

 

 

 

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    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