Combine data from different table into a new table

[Anonymous]

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 🙂

[parry2k]

@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. 

[Anonymous]

@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.

[dax]

Hi sebastienke,

You could create a blank query, then use below M code(which will choose corresponding column from different tables)

let
    Source = Table.Combine({Table.FromColumns({T1[User],T1[Serial]},{"Table1 Column1","Table1 Column2"}), 
    Table.FromColumns({T1[User],T1[Serial]}, {"Table1 Column1","Table1 Column2"}),
    Table.FromColumns({T2[userid],T2[id]}, {"Table1 Column1","Table1 Column2"})})
    in
    Source

Best Regards,
Zoe Zhi

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

[parry2k]

@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.