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.
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.
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
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.
We are excited to announce the Power BI Super Users!
Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.