Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi all,
I'm new to Power query (so, I apologize for stupid questions...), but I'm trying to compare sql query and power query, and I'm not able to merge these two tables.
table1: id_emp, name, salary
table2: id_cat, category, min_sal, max_sal
NO RELATION keys (intentionally) between tables.
In sql is very easy to solve:
select *
from table1, table2
where salary between min_sal and max_sal;
or, in ANSI,
select *
from table1 inner join table2
on salary between min_sal and max_sal;
How can I do the same thing in power query?!?
Thanks!
Solved! Go to Solution.
Hi @FabryZ ,
I downloaded the example file.
I updated the code provided by @Vera_33 and removed "each" from the syntax.
Copy the code below to a blank query to the example file and you know how it works.
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id_emp", Int64.Type}, {"name", type text}, {"salary", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Add Table 2", (x)=> Table.SelectRows( Table2, each x[salary]>[min_sal] and x[salary]<[max_sal])),
#"Expanded Add Table 2" = Table.ExpandTableColumn(#"Added Custom", "Add Table 2", {"id_cat", "min_sal", "max_sal"}, {"id_cat", "min_sal", "max_sal"})
in
#"Expanded Add Table 2"
Regards
KT
Hi @FabryZ,
All you need to do is add a custom column from with Table 1 or Table 2 then expand the columns.
in Table 1,
Result:
Please see below M code:
let
Source = Table1,
Custom1 = Source,
#"Added Custom" = Table.AddColumn(Custom1, "Add Table 2", each Table2),
#"Expanded Add Table 2" = Table.ExpandTableColumn(#"Added Custom", "Add Table 2", {"id_cat", "category", "min_sal", "max_sal"}, {"id_cat", "category", "min_sal", "max_sal"})
in
#"Expanded Add Table 2"
Regards
KT
Hi KT,
Yes, so I get a cross join and by adding this statement I solve:
# "Added Custom2" = Table.AddColumn (# "Expanded Add Table 2", "Cross_Join_cat", each if [salary]> = [min_sal] and [salary] <= [max_sal] then [id_cat] else null),
# "Filtered Rows" = Table.SelectRows (# "Added Custom2", each ([Cross_Join_cat] <> null)),
Thank you!
Hi @FabryZ ,
Sorry, I missed out the "WHERE" syntax.
I am glad you solved it.
@Vera_33's syntax work better for large dataset. What you need to do is add a custom column and write the code below instead of Table 2 (i.e. what I suggested). The syntax below is a function / nested / variable calcultion and return only the rows where the condition met.
(x)=> Table.SelectRows( Table2, each x[salary]>[min_sal] and x[salary]<[max_sal])
Regards
KT
Hi, @KT_Bsmart2gethe ,
I will try to solve the same exercise with Vera's syntax too, but I have to study how to call a nested function to generate the internal table. Thank you very much! 👋
Hi @FabryZ
Assume your Table2 is not big, add a custom column, buffer your Table2 if necessary
(x)=> Table.SelectRows( Table2, each x[salary]>[min_sal] and x[salary]<[max_sal])
Hi Vera, thank you.
Unfortunately I am unable to proceed further, because after creating the field, I am unable to recall table2 from the rows of table1. I have to study the use of function syntax. I'm really sorry, but thanks anyway!
Hi @FabryZ
my bad, when you paste it Power Query Editor will auto generate "each" then it becomes a Function...attached my file with dummy data for your reference
Hi Vera and thanks a lot for your patience! 🙂
Your attachment refers to power bi desktop software, my tables are in Excel.
So, I followed your previous instructions to successfully create the field, as in the linked example, but I was unable to connect function with table2. However, I resolved with:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Add Table 2", each Table2), as KT suggests, even if I am interested in your solution with function!
Best!
Hi @FabryZ ,
I downloaded the example file.
I updated the code provided by @Vera_33 and removed "each" from the syntax.
Copy the code below to a blank query to the example file and you know how it works.
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id_emp", Int64.Type}, {"name", type text}, {"salary", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Add Table 2", (x)=> Table.SelectRows( Table2, each x[salary]>[min_sal] and x[salary]<[max_sal])),
#"Expanded Add Table 2" = Table.ExpandTableColumn(#"Added Custom", "Add Table 2", {"id_cat", "min_sal", "max_sal"}, {"id_cat", "min_sal", "max_sal"})
in
#"Expanded Add Table 2"
Regards
KT
Hi @KT_Bsmart2gethe and @Vera_33 ,
You're right, and now I understand clarification of Vera, too! You have to be patient, I am really at the beginning!
Thank you very very much all! Have a great next Weekend!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.