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

thanks!

Some Microsoft employee has suggested (without giving any details) that:

1) I may import data to list, record or table - WITHOUT the need to "decompose" the logic.

2) then pass this list, record or table to the main table.

 

Using this approach he said that I will not need to change the logic (conditions).

I've tried several times, but fell. 

Unfortunately, he did not give me any working example.

 

Also I'm not that worried about "elegance", but the need to "decompose" something like below and then repeating it for 10 columns.

I can't use Perl, I can't use Qlik.

I have Power BI, Excel and dated Anaconda in my toolset.

 

if x>1 then y=2; z=3; w=w+1; y1=y1*2

  else ...

if x>5 then .... else ...

if (x>10 and (q<=4 or y=>3)) then .... else ...

 

 

 

 

 

 

 

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    This can be done easily in the query editor, but will require an if .. then .. else for each column (w, y, z, etc.).  It will work well and should be performant.  Are you trying to do if differently as an academic exercise to better understand M?  If so, I get it but not sure I want to go down this particular rabbit hole.

     

    Regards,

    Pat