Using UNION in DAX is a third option to combine data for analysis in Power BI
We recently showed how to do this in SQL
and how to do similar in Power Query
APPEND in Power Query
It isn’t always possible to do this in SQL such as if the datasets are coming from separate systems, and depending on the circumstances you may not want to do it in Power Query either. Doing it using DAX in Power BI to create the table is another alternative
UNION in DAX Syntax
Before you can use the DAX command, the tables you want to union must be in the data model
We will use the same two sample data tables that we used for the Append example last week
the first covers store numbers 1 to 5 (SalesA)
And the second covering store numbers 5 to 10 (SalesB)
to create a new UNION table, go into the table view pane and click 'New Table'The syntax is: New Table Name = UNION(table1,table2) We will call the new table 'Sales Combined' In the DAX formula bar we enter Sales Combined = UNION(SalesA,SalesB)
Once you press enter the DAX code runs and the new table is produced
We can see that the new table contain all 5 rows from SalesA and all 5 rows of SalesB
This is very straight forward in this example and simply combines the two datasets in a simple way
If you look carefully you can see that Store 5 Product 1 does have the same data for now we will assume these are not duplicates, but we will return to this to show how to deal with duplicates
Important points to note
For UNION to work there are a few considerations that must be in place, and these are essentially the same as UNION ALL in SQL
- The columns MUST be in the same order in both tables, if they are not it is possible that the UNION could still run, but give strange mixed results
- The data types of the columns must be compatible
- Column names are inherited from the first table, they do not have to be the same
- Tables must have the same number of columns
- Duplicates are not eliminated (we will explore this)
How to remove duplicates?
To demonstrate this we firstly need to remove our Source column
SELECTCOLUMNS(SalesA,"Product_ID",SalesA[Product_ID], "Store_ID", SalesA[Store_ID], "qty_sold", SalesA[qty_sold])
This DAX code just selects the three columns we need
We repeat this for both tables, then apply this into the union
Sales Combined = union(SELECTCOLUMNS(SalesA,"Product_ID",SalesA[Product_ID], "Store_ID", SalesA[Store_ID], "qty_sold", SalesA[qty_sold]),SELECTCOLUMNS(SalesB,"Product_ID",SalesB[Product_ID], "Store_ID", SalesB[Store_ID], "qty_sold", SalesB[qty_sold]))
We now see the 10 rows without the source column
To remove the duplicate for store 5, we can now use the DISTINCT command
Sales Combined = DISTINCT (union(SELECTCOLUMNS(SalesA,"Product_ID",SalesA[Product_ID], "Store_ID", SalesA[Store_ID], "qty_sold", SalesA[qty_sold]),SELECTCOLUMNS(SalesB,"Product_ID",SalesB[Product_ID], "Store_ID", SalesB[Store_ID], "qty_sold", SalesB[qty_sold])))
This results in 9 rows
You can see here that other table based operations can be used within UNION, here we use the select columns to only select the relevant columns for the union
This method would work with tables which have a different number of columns
UNION with more than two tables
Union needs to have at least 2 table references, but it is not limited to a fixed number
If we want to union three tables we just add more
Sales Combined Three = UNION(SalesA,SalesB,SalesA)
This example also shows that you can repeat the same table if you need to
Conclusion
With this third way to combine data tables in Power BI you are now equipped to perform your data consolidation
Having a seamless integration of multiple tables or queries is a great way to simplify onward analysis
Subscribe to our channel to see more SQL tips and timesavers
Select Distinct YouTube Channel
Or find other useful SQL, Power BI or other business analytics timesavers in our Blog
Our Business Analytics Timesavers are selected from our day to day analytics consultancy work and are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people. Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!
