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
ethinix
Dec 05, 2023Copper Contributor
Lorenzo The lattitude and logitude are missing in 1/3 of the file. Is it possible to include all the ZIP in one row based on the Physical City alone? I mean the first column.
Lorenzo
Dec 05, 2023Silver Contributor
Is it possible to include all the ZIP in one row based on the Physical City alone? Sure, see attached file
- LorenzoDec 05, 2023Silver ContributorYou're welcome & Thanks for posting feedback
Nice EOD...- ethinixDec 15, 2023Copper ContributorLorenzo I need some more help on this. Is it possible to modify the file to include the state also.
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.