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 

Your workbook is the same one I provided, which includes examples of the logic you initially described. I don't see any instructions for the full actual logic/steps you want to implement.

Phil

vyacheslavg's avatar
vyacheslavg
Helper II
5 years ago

PhilipTreacy 

Sorry, I want to implement the same code, as written initially:

 

if p1 = 1

then
  p2 = "a" // assign a new value to p2
  p3 = p3+1 // //increase p3 by 1

else
  p2 = "b" //assign a new value to p2
  p3 = p3+5 //increase p3 by 5

 

As far as I understand, the workflow could  be like this:

 

connect to Excel

import data into PQ data structure (list, record, or table)
Process data in PQ data structures

Extract (unwrap) data to columns.