Forum Discussion
Merge 3 tables (Append?)
Hello,
I have 3 different tables that I would like to merge into one. Actually from what I understand i need to do an append query but everything I tried is not working fine.
Below you find the structure from the 3 tables.
| Time | Site | Page | Format | Insertion | Insertion Type | Impressions | Inventory | Viewable Impressions | Revenue | Table |
| Time | Name (Site) | Name (Page) | Name (Format) | Name (Insertion) | Name (Insertion Type) | Impressions | Total inventory | Viewable impressions | Table1 | |
| Time | Name (Site) | Name (Page) | Name (Format) | Name (Insertion) | Name (Insertion Type) | RTB+ Impressions | Viewable impressions | RTB+ Gross revenue | Table2 | |
| Time | Name (Site) | Name (Page) | Name (Format) | HB Impressions | HB Revenue | Table3 |
I don't want to merge columns just need to append data from each column into the new merging table but each column needs to be appended to the right column. Some columns may be left empty.
The last column will be written depending the table. For example if this rows data belong to "table1" then write table 1, etc
Any suggestion?
Thank you,
Greg
3 Replies
Hi Greg,
Have a look at the attached PBIX.
The way I would typically deal with this is to skip the headers, append, add desired headers back on.
As for a column for the source table, add that prior to appending. Doing this dynamically will depend on the data source. May need some more info to assist with that, but let's start with the append.
Hello KNP,
Thank you a lot for the reply and the example!
What troubles me is that the 3 tables i have don't have the same structure. For example table 2 is missing Inventory Column. From what i've seen you are using as base "storeA" table at your example with
= Table.ColumnNames(StoreA)
So (i guess) i can't just append the 2nd and 3rd table to first since the structure isn't the same. Maybe a 1 to 1 matching is needed?
Thank you,
Grigoris
Sorry, I just reread your post, I understand the column structure better now.
I think what you actually need to do is rename the columns of each of the three tables prior to the append.
If each of the three files you have are at least consistent (individually) you could just do the rename on the three files and then append.
