Appending and Merging tables

[lastnn30]

Hi

I can merge 2 tables using Data Modeling of Power BI. I also understand that I can click on Transfer Data and merge 2 tables using Power Query. So what is the difference? 

Also can Data Modeling in Power BI, append tables (stack tables one on top of each other)? Or for that I have to use Power Query of Power BI? 

Thank you. 

[nvprasad · Accepted solution]

Hi lastnn30,

The merging or appending which we perform in Powerquery editor is the best practice as it compresses data to reduce the size of the file.

You can append tables in the PowerBI desktop using the "UNION" DAX function. Ex: Union (table 1, table 2).

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[lastnn30]

The Union idea is brilliant. Thank you so much all. I appreciate the help.

[v-rzhou-msft · Accepted solution]

Hi @lastnn30 ,

You can create a new merge table by join tables by key columns by Dax or by Power Query. If you do merge in Power Query, you don't need to create a new table so you can reduce the size of the file. Also, you can do some transform on the merged table. However, you couldn't see the merged table created by dax in Power Query. 

As far as I know, we couldn't append tables by Dax in Power BI. UNION function doesn't work like append.

For example, there are two tables, Table1 with ColumnA and Column B, Table2 with ColumnC and ColumnD. 

Append in Power Query will expand the new table and return a new table with four columns (A,B,C,D).

However, UNION will only return two columns based on left table. And the two tables must have the same number of columns.

So you have to use Power Query of Power BI to append tables.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nvprasad · Accepted solution]

Hi lastnn30,

The merging or appending which we perform in Powerquery editor is the best practice as it compresses data to reduce the size of the file.

You can append tables in the PowerBI desktop using the "UNION" DAX function. Ex: Union (table 1, table 2).

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad