Correct way to append/merge data files

[Anonymous]

Hi all, 

This is probably quite a simple query, but want to make sure I am doing it right. 

I have two files that are identifical in content/columns, etc - the only difference is that one covers 2016 - 2018, and the other from 2019 onwards; what is the correct way to append/merge these two so that they work on an existing PowerBI report (that was established for the 2016 - 2018 file). 

Many thanks! 

[Anand24 · Accepted solution]

Hi @Anonymous ,

You can simply append both the files from Power Query Editor:

1. Get both datasets to Power BI file

2. Go to Power Query Editor

3. Click on "Append Queries" or "Append Queries As New"

Append Queries: Over-writes the table currently

Append Queries As New: Creates new table

4.1. For Append Queries, Click on 1st dataset(In your case, it will be file with data from 2016-18) -> click on append queries and select the dataset in append(In your case, it will be file with data from 2019 onwards) in table to append dropdown

4.2. For Append Queries As New, Click on append queries -> Select Primary table as 1st dataset(In your case, it will be file with data from 2016-18) and table to append(In your case, it will be file with data from 2019 onwards)

5. Click OK

Note: For appending 3 or more tables in one go, a radio button will be available in append queries window.

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Ning_pbi]

@Anand24 , the solution doesn't seem to work if both tables are Direct Query mode.  Any suggestion if I want to do it in Direct Query?  Thanks a lot!

[Anonymous]

Thanks @Anand24 - that sounds like it makes sense, so I would use 'Append Queries' and it would essentially update the report as the table and fields would be the same but the new data would be included. Whereas if I used the 'Append as New' I would have to update the report fields to refer to the new table.

Is either method prefered? 

[Anand24]

Hi @Anonymous ,

Usually 'Append Queries' is used since it will automatically update data in the visuals where the fields from table/dataset is already used.

In cases where both tables are to be kept even after appending, the Append as new option is used.

Another general practise i have seen many times is using "Append as new" so as to check appending is done properly. If yes, then delete the new table that was created in "Append as new" step and then apply normal "append queries". I any issue is found in append as new table, then developers will debug it.

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[amitchandak]

@Anonymous , Not very clear. See if this can help

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