Forum Discussion
Performance + Understanding: UNION, Append, Merge
Merge is completely different from union and append queries. Merge does not create new rows in the data, it adds new columns to existing rows. So if you had 2 sources each with 4 columns, after merging and expanding them you would have 1 table with 8 columns.
Union and append are very similar to each other, except in how they identify columns, either by name or position. What they do is add all the rows from both tables together, so if you have 2 sources each with 4 rows then you would get 1 table with 8 rows.
It is generally considered best practice to do your data transformations as close to the source as possible. So you would perform a union in SQL if that was your data source. If using spreadsheets then doing an append in Power Query would probably be your next best bet. Finally you could use DAX union if you had to.
Using DAX can be useful if you want to create a shared dimension table from the values in 2 different tables, e.g.
Dimension Table = DISTINCT ( UNION ( DISTINCT ( 'table1'[name] ), DISTINCT ( 'table2'[name] ) ) )you could then link this new table to both table1 and table2 and use it as a shared dimension.
Other than for small tables like this you are probably better using Power Query. Another advantage of Power Query is that after you have done the append you can untick the "enable load" option for the base tables you have appended and then the data is only loaded into the model once and you don't have extra tables taking up space.
Merge is completely different from union and append queries. Merge does not create new rows in the data, it adds new columns to existing rows. So if you had 2 sources each with 4 columns, after merging and expanding them you would have 1 table with 8 columns.
Union and append are very similar to each other, except in how they identify columns, either by name or position. What they do is add all the rows from both tables together, so if you have 2 sources each with 4 rows then you would get 1 table with 8 rows.
It is generally considered best practice to do your data transformations as close to the source as possible. So you would perform a union in SQL if that was your data source. If using spreadsheets then doing an append in Power Query would probably be your next best bet. Finally you could use DAX union if you had to.
Using DAX can be useful if you want to create a shared dimension table from the values in 2 different tables, e.g.
Dimension Table =
DISTINCT ( UNION ( DISTINCT ( 'table1'[name] ), DISTINCT ( 'table2'[name] ) ) )
you could then link this new table to both table1 and table2 and use it as a shared dimension.
Other than for small tables like this you are probably better using Power Query. Another advantage of Power Query is that after you have done the append you can untick the "enable load" option for the base tables you have appended and then the data is only loaded into the model once and you don't have extra tables taking up space.