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 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
I noticed that some cities (For Eg. Akron) is present in all these states ALABAMA, COLORADO, INDIANA, IOWA, MICHIGAN, NEW YORK, OHIO, PENNSYLVANIA as such it
includes all the ZIPs of Akron city in one row.
Is it possible to include all the ZIP in one row based on the Physical City and State.
Thanks in advance.
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:
- LorenzoDec 16, 2023Silver ContributorYou're welcome & Thanks for providing feedback
- ethinixDec 16, 2023Copper ContributorThanks. Appreciate your help.
- LorenzoDec 16, 2023Silver Contributor
OK. Check the attached file. Should be good...
- ethinixDec 16, 2023Copper ContributorYes. It's Delivery Zip Code. Colum F.
- ethinixDec 15, 2023Copper Contributor
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