Forum Discussion
How to relate two tables without a common key (easy in SQL, but in Power Query?)
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!
Hi FabryZ ,
I downloaded the example file.
I updated the code provided by Anonymous 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
10 Replies
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
- KT_Bsmart2gethe
Impactful Individual
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!
- KT_Bsmart2gethe
Hi FabryZ ,
Sorry, I missed out the "WHERE" syntax.
I am glad you solved it.
Anonymous'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
