Performance + Understanding: UNION, Append, Merge

[Tontaube2]

Hi,

I seek more understanding than from the docs:

https://learn.microsoft.com/en-us/power-query/append-queries

~ if different columns/headers exist they are created and contain null values for the other table. Performs the append operation based on the names of the column headers found on both tables, and not based on their relative position in the headers sections of their respective tables.

https://learn.microsoft.com/en-us/power-query/merge-queries-inner

~ per row

https://learn.microsoft.com/en-us/dax/union-function-dax

~ The two tables must have the same number of columns. Columns are combined by position in their respective tables.

-> Is there a difference between UNION-Function and Power Query Append queries and Power Query -> Merge queries -> inner join, if both tables have the same number of columns, same headers, same position in their tables?

-> Which is better performance-wise?

Combine files queries: Applies the function query to the original query with input binaries (for example, the folder query). So it applies the function query for binary inputs on each row, and then expands the resulting data extraction as top-level columns.

-> Other than "binary": is it - like merge-queries->inner join - row-wise, whereas Append queries is column wise? How about the UNION-function?

-> Is it therefore "less performant" the larger the files are?

Bye

Michael

[johnt75 · Accepted solution]

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.