Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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...)
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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"
Here is the full example how I did it in Pandas.
How it works on a high level:
My take on how this can be implemented in M
So it should very similar, I just don't have enough knowledge about M.
Could not googled any results.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
Proud to be a Super User!
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.
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 ...
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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).
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |