Merging tables: First match condition (avoiding duplicates)

Hi Anonymous ,

I'm assuming that you're merging on [Country] and [City] in both tables. If you're merging on different columns, just reflect those columns in my instructions:

In your first table (the one with duplicates) add a grouped index:

1) Group by [Country] and [City] and use the All Rows operator for your aggregated column. Call the new column "data"

2) Add an index column to your nested tables by putting this as a custom step:

= Table.TransformColumns(
    previousStepName,
    { "data", each Table.AddIndexColumn(_, "Index", 1, 1) }
)

3) Click on the two outward pointing arrows at the top of your [data] column to reinstate all the rows we previously grouped together.

Now, just add a custom column to your second table, with the code '= 1' and call this column "mergeIndex""

Merge your tables on Table1 [Country], [City], [Index] = Table2 [Country], [City], [mergeIndex]

Pete

Now accepting Kudos!