Forum Discussion
Automatic tables merge and include new columns
Hello,
I have the following 2 tables in Power Query and i am trying to automatically merge them based on the Year and Week number and include any new columns.
Table A:
| Year | Week number | Stock | Returns | Defective |
| 2022 | 1 | 3 | 4 | 8 |
| 2022 | 2 | 6 | 9 | 17 |
| 2022 | 3 | 2 | 5 | 10 |
| 2022 | 4 | 9 | 3 | 16 |
| 2022 | 5 | 5 | 6 | 15 |
| 2023 | 6 | 2 | 2 | 10 |
| 2022 | 7 | 7 | 8 | 22 |
| 2022 | 8 | 3 | 3 | 13 |
| 2022 | 9 | 6 | 3 | 18 |
| 2022 | 10 | 3 | 3 | 14 |
| 2022 | 11 | 2 | 7 | 17 |
| 2022 | 12 | 3 | 7 | 22 |
| 2022 | 13 | 7 | 5 | 19 |
| 2022 | 14 | 9 | 8 | 18 |
| 2022 | 15 | 6 | 7 | 16 |
| 2022 | 16 | 7 | 14 | 34 |
Table B:
| Year | Week number | Sold | Excess | Expired |
| 2022 | 1 | 2 | 8 | 11 |
| 2022 | 2 | 6 | 2 | 10 |
| 2022 | 3 | 3 | 6 | 12 |
| 2022 | 4 | 3 | 5 | 12 |
| 2022 | 5 | 7 | 3 | 15 |
| 2023 | 6 | 2 | 3 | 11 |
| 2022 | 7 | 9 | 3 | 19 |
| 2022 | 8 | 4 | 7 | 13 |
| 2022 | 9 | 7 | 2 | 18 |
| 2022 | 10 | 8 | 9 | 14 |
| 2022 | 11 | 5 | 2 | 17 |
| 2022 | 12 | 3 | 6 | 21 |
| 2022 | 13 | 6 | 8 | 18 |
| 2022 | 14 | 9 | 18 | 17 |
| 2022 | 15 | 11 | 26 | 16 |
| 2022 | 16 | 12 | 3 | 23 |
This is what i did:
Table AB Merged:
| Year | Week number | Stock | Returns | Defective | Sold | Excess | Expired |
| 2022 | 1 | 3 | 4 | 8 | 2 | 8 | 11 |
| 2022 | 2 | 6 | 9 | 17 | 6 | 2 | 10 |
| 2022 | 3 | 2 | 5 | 10 | 3 | 6 | 12 |
| 2022 | 4 | 9 | 3 | 16 | 3 | 5 | 12 |
| 2022 | 5 | 5 | 6 | 15 | 7 | 3 | 15 |
| 2023 | 6 | 2 | 2 | 10 | 2 | 3 | 11 |
| 2022 | 7 | 7 | 8 | 22 | 9 | 3 | 19 |
| 2022 | 8 | 3 | 3 | 13 | 4 | 7 | 13 |
| 2022 | 9 | 6 | 3 | 18 | 7 | 2 | 18 |
| 2022 | 10 | 3 | 3 | 14 | 8 | 9 | 14 |
| 2022 | 11 | 2 | 7 | 17 | 5 | 2 | 17 |
| 2022 | 12 | 3 | 7 | 22 | 3 | 6 | 21 |
| 2022 | 13 | 7 | 5 | 19 | 6 | 8 | 18 |
| 2022 | 14 | 9 | 8 | 18 | 9 | 18 | 17 |
| 2022 | 15 | 6 | 7 | 16 | 11 | 26 | 16 |
| 2022 | 16 | 7 | 14 | 34 | 12 | 3 | 23 |
But the problem is that if new columns are added to the tables, then they are not automatically reflected into the merged table. I have to manually select the new columns.
For example, if a new column is added to Table B, called Overdue, then it is not automatically included into the Merged table.
Is there a way to fix this? Any help is much appreciated!
Hi Anonymous ,
try
Table.ExpandTableColumn( Source, "Table B", List.Select( Table.ColumnNames(#"Table B"), each _ <> "Year" and _ <> "Week Number" ) )
7 Replies
- KT_Bsmart2gethe
Impactful Individual
Hi Anonymous ,
The link below is the video show you how to do dynamic expanding columns:
Regards
KT
KT_Bsmart2gethe Thanks for your reply. I tried to adapt it to the two tables A and B above, but i couldn't get the correct results.
This is the line that should be replaced:
= Table.ExpandTableColumn(Source, "Table B", {"Sold", "Excess", "Expired"}, {"Sold", "Excess", "Expired"})This is my attempt and but there are some issues:
1. how to remove the prefix for the second table's columns?
2. how to keep only one copy for Year and Week number columns?
= Table.ExpandTableColumn(Source, "Table B", Table.ColumnNames(#"Table B"), List.Transform(List.Select(Table.ColumnNames(#"Table B"), each not Text.Contains(_,"Suburb")), each "City_List."&_))Results:
Year Week number Stock Returns Defective City_List.Year City_List.Week number City_List.Sold City_List.Excess City_List.Expired 2022 1 3 4 8 2022 1 2 8 11 2022 2 6 9 17 2022 2 6 2 10 2022 3 2 5 10 2022 3 3 6 12 2022 4 9 3 16 2022 4 3 5 12 2022 5 5 6 15 2022 5 7 3 15 2023 6 2 2 10 2023 6 2 3 11 2022 7 7 8 22 2022 7 9 3 19 2022 8 3 3 13 2022 8 4 7 13 2022 9 6 3 18 2022 9 7 2 18 2022 10 3 3 14 2022 10 8 9 14 2022 11 2 7 17 2022 11 5 2 17 2022 12 3 7 22 2022 12 3 6 21 2022 13 7 5 19 2022 13 6 8 18 2022 14 9 8 18 2022 14 9 18 17 2022 15 6 7 16 2022 15 11 26 16 2022 16 7 14 34 2022 16 12 3 23 - KT_Bsmart2gethe
Hi Anonymous ,
1. how to remove the prefix for the second table's columns?
Remove the RED below will remove the prefix.
2. how to keep only one copy for Year and Week number columns?
Add the BLUE below will remove the Year and Week columns.
Table.ExpandTableColumn(Source, "Table B", Table.ColumnNames(#"Table B"), List.Transform(List.Select(Table.ColumnNames(#"Table B"), each _ <> "Year" and _ <>"Week Number" not Text.Contains(_,"Suburb")), each "City_List."&_))
The corrected code to your case:
Table.ExpandTableColumn( Source, "Table B", Table.ColumnNames(#"Table B"), List.Select( Table.ColumnNames(#"Table B"), each _ <> "Year" and _ <> "Week Number" ) )Regards
KT