Combining 5 different dataset in 1 table

[xRTP]

Hello, Im having trouble between Append and Merge queries since all of my 5 dataset some with same column some weren't the same column. I've try to append but I notice that some of the data/values move to different column even if they have the same column with the other 2 (1st table have timeliness and 2nd table have timeliness column) on 2nd table the data/value on the timeliness column were moved into the 3rd column (metrics column) same scenario with the other column, Im actually confuse between append and merge. If i did use Merge queries it needs to have a same row data but the thing is I dont have any unique identifier and im only using the name column to join the 1st and 2nd table. So for example if name "Clyde" is on the 1st table and it doesn't appear on the 2nd table, does it mean that the data for "Clyde" wont be add to the Merge table?

My plan is to combine all 5 dataset to 1 table and it will consist all of the columns of the 5 dataset. I've read some article about append and merge sorry im kinda slow and easily get confuse. Need your help and thanks in advance!

[amitchandak]

@xRTP , If you want Union all, then you need append.

In the case of append makes sure the column name match in Case too. Rename, remove space etc and then append

Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[xRTP]

Hello, i've tried to append but im having missmatch of data as i've said above. Here's the list of columns i have. The values were moving to different column. The thing is if the column timeliness is present the data from 1st table and 2nd table will be put under timeliness column but if other column werent present it will add to the right.

[v-yiruan-msft · Accepted solution]

Hi @xRTP ,

Please check if there is any space in the column [timeliness] in 1st and 2nd table(For example: [timeliness]  and [timeliness ] ), and it may cause the column [timeliness] in these two tables be identified as the column with different column names... In order to make troubleshooting and give you a suitable solution, could you please provide a simplified pbix file with only involved tables? Before share the file with us, please exclude the sensitive info in the file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

And you can review the following links to get more details of merge vs append and know the applied scenario of merge and append query...

Merge Vs. Append Concepts in Power BI (Power Query)

Append query: Combine rows from two or more tables one after another. Structure of the columns of those table should be same.

Merge query:  Combine multiple queries horizontally based on standard criteria (common column) between the tables.

Append query

Merge Query

Power BI Merge Queries & Append Queries

Best Regards

[xRTP]

Right on point!! Thank you very much!!