How to relate two tables without a common key (easy in SQL, but in Power Query?)

[FabryZ]

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!

[KT_Bsmart2gethe]

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

[FabryZ]

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.

@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

[FabryZ]

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

[Vera_33]

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])

[FabryZ]

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!

[Vera_33]

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

[FabryZ]

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!

Example.xlsx 

[KT_Bsmart2gethe · Accepted 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

[FabryZ]

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!