cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors