Forum Discussion
How to relate two tables without a common key (easy in SQL, but in Power Query?)
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
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!
