From procedural language to Power query

[vyacheslavg]

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 pseudocode:

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

Above could be easily implemented in Perl or Python. And actual code will look almost like this pseudocode.

But is it possible to implement similar in Power Query? Variables would be a columns in a dataset. Example of such dataset is below

Dropbox link to a simple Excel file

https://www.dropbox.com/s/qm9plz4i82czk7x/test1.xlsx?dl=0

Input

p1	p2	p3
1	x	7
2	y	8
1	z	5

The desired outcome|output after Power Query applied.

p1	p2	p3
1	a	8
2	b	13
1	a	6

I understand only one way - I need to create two columns (p2, p3), in which I need to repeat if-then-else logic.

This is very un-elegant and confusing way.

Is there a way to write it "compact" code, with single if-then-else statement as in above pseudo-code?

PS I know, that I can use Python (Pandas) to import and change the data on the fly.
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts
But I want to do it in Power Query only (maybe using lists or records, I don't know...)

[vyacheslavg]

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"

[vyacheslavg]

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 · Accepted solution]

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]

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]

@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.

[vyacheslavg]

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]

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

[vyacheslavg]

@mahoneypat 

Could you please provide an example?

https://d13ot9o61jdzpp.cloudfront.net/files/pbiforum/vyacheslavg.xlsx

Even if I need to copy existing logic over each column...

The major "wish" from my side - is that I don't want to change the logic, just copy/paste the logic from Perl. Even if I have to copy it several times (sigh). 

[vyacheslavg]

Hi @PhilipTreacy 

Thanks, but unfortunately, I came up with almost the same solution. 

The problem is  - these _if-then-else_ conditions could be much more complicated (sometimes up to 30 conditions).

If I try to "decompose" or "unpiwot" conditions for each variable, it will be very, very time-consuming and hard to maintain in case of any change. It is not easy even for this very simplified example, but in a real-life situation it would be much harder.

Even the  pseudo-code does not look nice (repetition...) 😞

if p1 = 1
then
  p2 = "a" else p2 = "b"

if p1 = 1
then 
  p3 = p3+1 else p3 = p3+5

[mahoneypat]

You could return a record, list, table, and/or function from a single if expression that has all the replacement values and/or values to add.  However, transforming all the needed columns with those values will be tricky (but probably doable).  My guess is that you would also not consider it elegant.  I also recommend the standard approach.

Regards,

Pat

[PhilipTreacy]

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.