cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HawkB
Resolver I
Resolver I

Correct way to append/merge data files

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! 

1 ACCEPTED SOLUTION
Anand24
Super User
Super User

Hi @HawkB ,

 

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"

append1.PNG

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 !!!

View solution in original post

5 REPLIES 5
Anand24
Super User
Super User

Hi @HawkB ,

 

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"

append1.PNG

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 !!!

@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!

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? 

Hi @HawkB ,

 

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
Super User
Super User

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors