How do I merge tables with common ID values without having value repeat ?

[NZEmersen]

Hi,

I have three tables showing Plant, Material and Labour quantities from each of my crews on site,

The tables are linked by unique identifiers or "Shift Report Numbers", however, multiple items assigned to each unique identifier. For example:

Shift Report Number	Date	Job Code	Labour Resource	Hours
00001	01/01/21	XYZ	Harry	2
00001	01/01/21	XYZ	Jimmy	3
00001	01/01/21	XYZ	Harold	3

Shift Report Number	Material	Quantity
00001	Aggregate	200

Shift Report Number	Plant	Quantity
00001	Roller	1
00001	Digger	1
00001	Plate Compactor	0.5

I would like to merge the tables, however, have each observation from each table only occur once in the combined result and be justified to the top of the table. i.e:

Shift Report Number	Date	Job Code	Labour Resource	Hours	Material	Quantity	Plant	Quantity
00001	01/01/21	XYZ	Harry	2	Aggregate	200	Roller	1
00001	01/01/21	XYZ	Jimmy	3			Digger	1
00001	01/01/21	XYZ	Harold	3			Plate Compactor	0.5

The individual tables are imported from local files on my computer using Power Queries. I have tried to use Power Queries and Full Outer Merges to combine the tables but have found that observations are repeated from each table.

How can I produce the desired result ?

Many Thanks

 

[sandy666]

Shift Report Number	Date	Job Code	Labour Resource	Hours
00001	01/01/2021	XYZ	Harry	2
00001	01/01/2021	XYZ	Jimmy	3
00001	01/01/2021	XYZ	Harold	3
Shift Report Number	Material	Quantity
00001	Aggregate	200
Shift Report Number	Plant	Quantity
00001	Roller	1
00001	Digger	1
00001	Plate Compactor	0.5
Shift Report Number	Date	Job Code	Labour Resource	Hours	Material	Quantity	Plant	Quantity.1
00001	01/01/2021	XYZ	Harry	2	Aggregate	200	Roller	1
00001	01/01/2021	XYZ	Jimmy	3			Digger	1
00001	01/01/2021	XYZ	Harold	3			Plate Compactor	0.5

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    Join1 = Table.NestedJoin(Index,{"Index"},Table2,{"Index"},"Table2",JoinKind.LeftOuter),
    Expand1 = Table.ExpandTableColumn(Join1, "Table2", {"Material", "Quantity"}, {"Material", "Quantity"}),
    Join2 = Table.NestedJoin(Expand1,{"Index"},Table3,{"Index"},"Table3",JoinKind.LeftOuter),
    Expand2 = Table.ExpandTableColumn(Join2, "Table3", {"Plant", "Quantity"}, {"Plant", "Quantity.1"}),
    RC = Table.RemoveColumns(Expand2,{"Index"}),
    Type = Table.TransformColumnTypes(RC,{{"Date", type date}})
in
    Type

// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1)
in
    Index

// Table3
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1)
in
    Index

 

[NZEmersen]

Shift Report Number	Date	Job Code	Labour Resource	Hours
00001	01/01/2021	XYZ	Harry	2
00001	01/01/2021	XYZ	Jimmy	3
00001	01/01/2021	XYZ	Harold	3
Shift Report Number	Material	Quantity
00001	Aggregate	200
Shift Report Number	Plant	Quantity
00001	Roller	1
00001	Digger	1
00001	Plate Compactor	0.5
Shift Report Number	Date	Job Code	Labour Resource	Hours	Material	Quantity	Plant	Quantity.1
00001	01/01/2021	XYZ	Harry	2	Aggregate	200	Roller	1
00001	01/01/2021	XYZ	Jimmy	3			Digger	1
00001	01/01/2021	XYZ	Harold	3			Plate Compactor	0.5

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    Join1 = Table.NestedJoin(Index,{"Index"},Table2,{"Index"},"Table2",JoinKind.LeftOuter),
    Expand1 = Table.ExpandTableColumn(Join1, "Table2", {"Material", "Quantity"}, {"Material", "Quantity"}),
    Join2 = Table.NestedJoin(Expand1,{"Index"},Table3,{"Index"},"Table3",JoinKind.LeftOuter),
    Expand2 = Table.ExpandTableColumn(Join2, "Table3", {"Plant", "Quantity"}, {"Plant", "Quantity.1"}),
    RC = Table.RemoveColumns(Expand2,{"Index"}),
    Type = Table.TransformColumnTypes(RC,{{"Date", type date}})
in
    Type

// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1)
in
    Index

// Table3
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1)
in
    Index

Apologies,

I am quite new to using power queries in the way you have shown,

How to I implement the above code ?

Many Thanks,

 

[sandy666]

How to I implement the above code ?

you don't need to do this
just add Index (the same style) to each table
then merge Table1 and Table2 by Index, Expand without ID and Index and then merge Table1 and Table3 by Index and Expand without ID and Index
Remove unnecessary column - first Index
that's all

 

[sandy666]

here is your example

s666-PQ-merge 3 tables with index.xlsx

Microsoft Excel Workbook

1drv.ms