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 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!
Impactful IndividualHi 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
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! 👋