Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

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

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

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!

Anonymous
Not applicable

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

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

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.