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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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