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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
navafolk
Helper IV
Helper IV

Append selected columns to Master table, with different headers in Power Query

Hi pros,

I had an exercise with Power Query, appending Sub tables to Master tables which are different in size, in header names and selected columns only. Master table and other Sub tables. Master table needs a concatenation of data from other Sub tables. However, due to different systems, the table size/header names are different.

- Master table looks like:

navafolk_0-1725592371411.png

Vendor_IDAmountCodeLocationType
AAA10VVXsouthw
BBB50DVXnorthr

 

- Sub_1 table looks like:

navafolk_1-1725592408165.png

NameGenOutstanding
ABC612
BBB77

 

The concatenation/appending rules would be:

- [Name] in Sub_1 would be appended to [Vendor_ID] in Master

- [Outstanding] in Sub_1 would be appended to [Amount] in Master

- [Code] in Master for Sub_1 part would be table's name or any text e.g. Sub_1

- Other columns would be left blank.

 

Desired output would look something like this:

navafolk_2-1725592423945.png

Vendor_IDAmountCodeLocationType
AAA10VVXsouthw
BBB50DVXnorthr
ABC12Sub_1  
BBB7Sub_1  

 

Thank you for your help.

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

Hi,

Thanks for the solution rajendraongole1 offered, and i want to offer some more information for user to refet to.

hello @navafolk , if you want to append the data, you need to have the columns that have same name in each table, so you need to make sure that   column names  in the sub table to are the same as the column names in main table thay you want to apped.

and if you want to include the sub table name in main table, you can consider to put the data files in a folder, then use the foloder connector , then it will display all table names, you can refer to the following link.

Power Query Folder connector - Power Query | Microsoft Learn

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
v-xinruzhu-msft
Community Support
Community Support

Hi,

Thanks for the solution rajendraongole1 offered, and i want to offer some more information for user to refet to.

hello @navafolk , if you want to append the data, you need to have the columns that have same name in each table, so you need to make sure that   column names  in the sub table to are the same as the column names in main table thay you want to apped.

and if you want to include the sub table name in main table, you can consider to put the data files in a folder, then use the foloder connector , then it will display all table names, you can refer to the following link.

Power Query Folder connector - Power Query | Microsoft Learn

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

rajendraongole1
Super User
Super User

Hi @navafolk - You can achieve this in Power Query by transforming the Sub_1 table to match the structure of the Master table and then appending the two tables.

Please find the attached pbix file.

 

Hope this helps.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Thank you @rajendraongole1 for your response.

It is hard to transform Sub_1 because it is used for other reports and calculations. The duplication of Sub_1 for transforming is somehow hard in my case because of heavy data in practice.

The help of any M query functions would be great @rajendraongole1 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!