Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FabryZ
Frequent Visitor

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!

1 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"

 

KT_Bsmart2gethe_0-1652313812764.png

 Regards

KT

View solution in original post

10 REPLIES 10
KT_Bsmart2gethe
Impactful Individual
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,

KT_Bsmart2gethe_0-1652178766697.png

 

Result:

KT_Bsmart2gethe_1-1652178831017.png

 

 

 

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

Vera_33
Resident Rockstar
Resident Rockstar

Hi @FabryZ 

 

Assume your Table2 is not big, add a custom column, buffer your Table2 if necessary

Vera_33_0-1652057561472.png

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

Vera_33
Resident Rockstar
Resident Rockstar

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!

Example.xlsx 

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"

 

KT_Bsmart2gethe_0-1652313812764.png

 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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors