Forum Discussion

ethinix's avatar
ethinix
Copper Contributor
Dec 05, 2023
Solved

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...
  • Lorenzo's avatar
    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 CITYLatitudeLongitude
    BERLIN52.524413.4105
    AMSTERDAM52.524413.4105
    AMSTERDAM52.524413.4105

Resources