Merge Columns

[Harvey85]

Hello,

I have the following table in Power Query:

Key		Plant_Material	Plant_Area		Seed_Date		Seed_Total
AA1123		PP STAFF 1	15		30/11/2023		149
AA1123		PP STRINGS 2	134		30/11/2023		149

The issue is that I'm building a report where I'm adding to that table (with Merge button), the following columns by Key:

Key		Replant_Material		Replant_Area		Replant_Date
AA1123		XR MAPS 3		11		23/01/2024
AA1123		QP SPLT 2		93		23/01/2024

But the problem is that I end up with the followin table with 4 rows instead of 2:

Key

Plant_Material

Plant_Area

Seed_Date

Plant_Area_Total

Replant_Area

Replant_Date

Replant_Material

AA1123

PP STAFF 1

15

30/11/2023

149

11

23/01/2024

XR MAPS 3

AA1123

PP STRINGS 2

134

30/11/2023

149

11

23/01/2024

XR MAPS 3

AA1123

PP STAFF 1

15

30/11/2023

149

93

23/01/2024

QP SPLT 2

AA1123

PP STRINGS 2

134

30/11/2023

149

93

23/01/2024

QP SPLT 2

And I should keep the 2 rows with something like this:

Key

Plant_Material

Plant_Area

Seed_Date

Plant_Area_Total

Replant_Area

Replant_Date

Replant_Material

AA1123

PP STAFF 1

15

30/11/2023

149

11

23/01/2024

XR MAPS 3

AA1123

PP STRINGS 2

134

30/11/2023

149

93

23/01/2024

QP SPLT 2

I believe there is not a minimal key to join both tables by Key+Material because I'm building that table as a flat table with all columns, but is there any way to add those column so that my original table don't add more rows?

Thank you!!!

[HotChilli · Accepted solution]

You could add an index column to both tables and Merge on that. The order of both tables will have to be correct before adding the index.