Forum Discussion
How to merge multiple rows with same data except one column with different data.
- Dec 05, 2023
Hi ethinix
With Power Query. Grouping of records based on Latitude & Longitude:
// ZipCodesConcat let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], SourceNames = Table.ColumnNames(Source), Keys = {"Latitude", "Longitude"}, TypedZipCode = Table.TransformColumnTypes(Source, {{"DELIVERY ZIPCODE", type text}} ), GroupedByLatLong = Table.Group(TypedZipCode, Keys, { {"DELIVERY ZIPCODE", each Text.Combine([DELIVERY ZIPCODE], ", ")}, {"DATA", each Table.FirstN(_, 1), type table} } ), Expanded = Table.ExpandTableColumn(GroupedByLatLong, "DATA", List.Difference(SourceNames, Keys & {"DELIVERY ZIPCODE"}) ), Reordered = Table.SelectColumns(Expanded, SourceNames) in Reordered
In attached file:
- Put your data in Table1 (sheet 'Input'), adding your extra column(s) that don't appear on your picture
- Switch to sheet 'Output' > Right-click in table ZipCodesConcat > Refresh(EDIT) Assumptions made:
#1 [Latitude] + [Longitude] are always provided (no blank/null)
#2 The input doesn't contain something like (otherwise the grouping must be revised):PHYSICAL CITY Latitude Longitude BERLIN 52.5244 13.4105 AMSTERDAM 52.5244 13.4105 AMSTERDAM 52.5244 13.4105
Hi ethinix
With Power Query. Grouping of records based on Latitude & Longitude:
// ZipCodesConcat
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SourceNames = Table.ColumnNames(Source),
Keys = {"Latitude", "Longitude"},
TypedZipCode = Table.TransformColumnTypes(Source,
{{"DELIVERY ZIPCODE", type text}}
),
GroupedByLatLong = Table.Group(TypedZipCode, Keys,
{
{"DELIVERY ZIPCODE", each Text.Combine([DELIVERY ZIPCODE], ", ")},
{"DATA", each Table.FirstN(_, 1), type table}
}
),
Expanded = Table.ExpandTableColumn(GroupedByLatLong, "DATA",
List.Difference(SourceNames, Keys & {"DELIVERY ZIPCODE"})
),
Reordered = Table.SelectColumns(Expanded, SourceNames)
in
Reordered
In attached file:
- Put your data in Table1 (sheet 'Input'), adding your extra column(s) that don't appear on your picture
- Switch to sheet 'Output' > Right-click in table ZipCodesConcat > Refresh
(EDIT) Assumptions made:
#1 [Latitude] + [Longitude] are always provided (no blank/null)
#2 The input doesn't contain something like (otherwise the grouping must be revised):
PHYSICAL CITY | Latitude | Longitude |
BERLIN | 52.5244 | 13.4105 |
AMSTERDAM | 52.5244 | 13.4105 |
AMSTERDAM | 52.5244 | 13.4105 |