Forum Discussion
Combine data from different table into a new table
Hi PBI Community
I've looked for similar topics but I didn't find one related to my request...
I have 3 different tables with the same kind of data in each of them:
- Date of arrival of the container at the port
- the week number related to this date
- the number of pallets inside.
Each line is a different container.
Each table is from a different container company.
My goal is to gather the number of containers and pallets by week.
Table 1:
Date1 | Week1 | Pallets1
3 sept | W37 | 12
3 sept | W37 | 10
12 sept | W38 | 20
19 sept | W39 | 25
Table 2:
Date2 | Week2 | Pallets2
4 sept | W37 | 20
12 sept | W38 | 10
20 sept | W39 | 15
Table 3:
Date3 | Week3 | Pallets3
3 sept | W37 | 18
12 sept | W38 | 12
19 sept | W39 | 10
20 sept | W39 | 15
I would like to create the table below:
Week | number of containers | number of pallets
37 | 4 | 60
38 | 3 | 42
39 | 4 | 65
Any help on this ?
Thanks a lot :)
4 Replies
Anonymous it will be easier if you append all the tables in power query and visualize from there. You will have one big table with all the rows in it and be straight forward from there.
parry2k you're totally right, that's what I would have done if my table were as simple as described.
Indeed, I simplified the problem to make it more understanble. Actually, those tables are really bigger and not exactly structured the same way for each of them (because coming from different companies). But I can find those 3 kind of data in each of them, and I want to compil this. Do you have an idea how to it ?
Thanks for your help.
Anonymous wel you first have to transform each table to get to the shape you described in example. Remove extra columns you don't need and rename the column , and then use the append.
