Forum Discussion
ethinix
Dec 05, 2023Copper Contributor
How to merge multiple rows with same data except one column with different data.
In the image above, I have multiple rows of data which are the same across all columns except one, that is Delivery Zip Code, where the data is different on all rows. I want to merge these 4 r...
- 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