Merge 2 tables in Power Query based on multiple conditions

[Mond]

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.

[Anand24]

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

[Anand24]

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

[Mond]

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.

[mhossain]

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.

[ImkeF]

Hi @Mond ,

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

[Greg_Deckler]

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

If not @ImkeF 

[Mond]

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.