cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heidilucytaylor
New Member

Newbie! struggling to link 2 sets of data with no real relationship apart from similar columns

Hi, i am a total novice, ive had a chat with the lad in the office who has some knowledge of PBI but not enough to sort this issue out.

 

I have 2 sets of Data: Quotes & Orders

Both have the same column names (sort of) but nothing specific to link a quote to an order (ie Quote number on Order)

 

But i want to create a page with a Smart filter that will filter both sets of data on the columns im selecting, ie Department.

 

I have been workin on creating a specific data list for each column then linking a relationship, this seems really long winded and MUST be a better way that i am just blind to.

 

Thank you!! and please help - pressure from boss to give him something

 

5 REPLIES 5
Anonymous
Not applicable

Hi- 

In your Power Query editor select the table that has the most deparments (likely your Order Table) 

 

Right Click on the Department Column and Click "Add as new Query" 

 

Go to this new list and removed the duplicates 

 

Canvert list to a Table and Name it "Departments" 

 

Close &Apply your Power Query Editor

 

Now in your relationships link the Departments to both your Quote[Departments] and your Order[Departments] 

 

and voila 

Thanks Sailkitty, this works well untill i want to link many many columns.

i was wondering if there was a better way of doing this but maybe not?

Anonymous
Not applicable

Could you give me an example? 

 

You could contenate some columns first and the do the method decribed. 

 

But with out understanding the context a it abit more thats all I can say 

ezpowerbi
Frequent Visitor

Can u share sample tables with some fictitious data to give to exact solution?

themistoklis
Super User
Super User

@heidilucytaylor

Concatenate the common columns on both datasets and add the concatenation to a new column

 

Then join the 2 datasets using the concatenated column.

 

Then any filter added will filter both datasets

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors