cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Merge 2 tables in Power Query based on multiple conditions

Hi,

 

I am trying to achieve sql condition in Power Query to merge 2 different tables based on below conditio:

 

Table A.Column1 = Table B.Column1 and ( Table A.Column2 = Table B.Column2 OR Table A.Column3 = Table B.Column3)

 

When I do merge on 2 tables, all I can do is choose which columns to merge with and expand the table but default option is AND , there's no OR option or way to define my requested condition.
Can some one help on this.

 

Thanks in advance.

6 REPLIES 6
Super User II
Super User II

Hi @Mond ,

 

AND condition be achieved in the following way:

1. Create a new column in both tables by concatenating the columns in Join condition.

Say you are joining on Table A.Column1 = Table B.Column1 AND Table A.Column2 = Table B.Column2, then create a new column by concatenating Column1 and Column 2

2. Join both tables on the new column created in step 1

 

This can be done for any no. of AND conditions by new column with concatenating fields from joining condition.

 

Not quite sure about OR condition joining. I don't think it currently exists in Power BI. Hence these conditions are usually managed by changing the structure of datasets used.

You can probably submit this as an idea here.

 

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

Thanks Anand for your prompt input.

I have done that approach before coming to community, since there's no OR condition, I couldn't get the data I want to represent.

I will submit an idea and probably you can vote to give more visibility.

Thanks once again.

Super User I
Super User I

Hi @Mond 

How about you just apply first merge "Table A.Column1 = Table B.Column1" and bring column2, column3 and other columns from Table B.

 

Once you have all the data in Table A, you add one custom column and write dax for the sql condition you mention and bring maybe 'Yes'/'No' or something. And now you know which records you need to consider for the calculation in the columns from TAble B.

Hope this is making sense.

Hi @Mond ,

I cannot think of a better solution than what  @Greg_Deckler  said.

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Super User IV
Super User IV

@Mond - Maybe merge on the first set of columns, merge on the second set of columns. Append, remove duplicates.

 

If not @ImkeF 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi,

 

When you choose first set and second set of columns on merge, it will be AND condition, there's no option to have OR condition.

May be I am missing something in the helpful hint.

 

Thank you in advance.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

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

Top Solution Authors
Top Kudoed Authors
Users online (685)