Forum Discussion
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:
| Vendor_ID | Amount | Code | Location | Type |
| AAA | 10 | VVX | south | w |
| BBB | 50 | DVX | north | r |
- Sub_1 table looks like:
| Name | Gen | Outstanding |
| ABC | 6 | 12 |
| BBB | 7 | 7 |
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:
| Vendor_ID | Amount | Code | Location | Type |
| AAA | 10 | VVX | south | w |
| BBB | 50 | DVX | north | r |
| ABC | 12 | Sub_1 | ||
| BBB | 7 | Sub_1 |
Thank you for your help.
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.
3 Replies
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.
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
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.
