Forum Discussion

Mond's avatar
Mond
Icon for Helper III rankHelper III
6 years ago

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.

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

     

    If not ImkeF 

    • Mond's avatar
      Mond
      Icon for Helper III rankHelper III
      6 years ago

      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.

  • mhossain's avatar
    mhossain
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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.

  • Anand24's avatar
    Anand24
    Icon for Super User rankSuper User
    6 years ago

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

    • Mond's avatar
      Mond
      Icon for Helper III rankHelper III
      6 years ago

      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.