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

[heidilucytaylor]

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

[Anonymous]

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 

[heidilucytaylor]

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]

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]

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

[themistoklis]

@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