Forum Discussion
How to merge multiple rows with same data except one column with different data.
- Dec 04, 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 ReorderedIn 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
Is it possible to include all the ZIP in one row based on the Physical City and State
In principal that should be doable but I don't see a [State] field in the latest file I used:
I am attaching herewith the sample file which has all the existing fields. Kindly use this file to amended file the file that you sent me.
Many Thanks
- LorenzoDec 16, 2023Silver ContributorYou're welcome & Thanks for providing feedback
- ethinixDec 16, 2023Copper ContributorThanks. Appreciate your help.
- LorenzoDec 15, 2023Silver Contributor
OK. Check the attached file. Should be good...
- ethinixDec 15, 2023Copper ContributorYes. It's Delivery Zip Code. Colum F.