Forum Discussion
Union all with multiple table joins
I want to be able to do the following in powerbi without using sql (I.e by using the power bi data sources, modeling relationships and dax etc)
Select col1,col2
from tableA
Join tableB on
TableA.key1= tableB.key1
Union all
Select col1,col2
from tableC
Join tableD on
TableC.key1= tableD.key1
Does anyone know how this can be achieved by not using sql in powerbi?
Thanks
John
Yep this is basic Power Query
there is two simple functions in the Query editor you need to understand to make this work
Merge Queries (JOIN in SQL)
Append Queries (UNION)
to achieve this you would create a connnection to Table A and Table B then Merge A to Table B via the key cols and expand the cols in Table B that you need
Then same method for Table C -> D
Then Append results from Table A to Results from Table C
I hope this makes sense.. the down side of using power query in this way is that it needs to bring in all 4 tables to make it work, hence using SQL to do the joins would be more efficient.
Greg
you can shift + select columns to merge in order (a little 1, 2, 3 etc will appear on the columns), select the equivalent columns in the same order to complete the merge
3 Replies
Yep this is basic Power Query
there is two simple functions in the Query editor you need to understand to make this work
Merge Queries (JOIN in SQL)
Append Queries (UNION)
to achieve this you would create a connnection to Table A and Table B then Merge A to Table B via the key cols and expand the cols in Table B that you need
Then same method for Table C -> D
Then Append results from Table A to Results from Table C
I hope this makes sense.. the down side of using power query in this way is that it needs to bring in all 4 tables to make it work, hence using SQL to do the joins would be more efficient.
Greg
- Thanks for the explanation. I had tried the merge but it was only giving me an option to join two tables. I need to join 5 in each select.
Is that a limitation that the join key can only be one field like in the modeller (where you have to merge key fields in to one field in each table)you can shift + select columns to merge in order (a little 1, 2, 3 etc will appear on the columns), select the equivalent columns in the same order to complete the merge
