Forum Discussion

VividInk225's avatar
VividInk225
Copper Contributor
Sep 16, 2021

Assistance in consolidating tracking numbers to one line/cell

A customer recently requested that our tracking reports be adjusted so that there is one row per location with it's tracking numbers all within ONE cell. (Currently each site has one or many rows depending on the number of tracking numbers associated with that location). See attached documents 

 

Any help would be greatly appreciated! 

excel

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 16, 2021

    VividInk225 

    Few ways, some of them

    1) Excel 365 formulas

    unique customers

    =UNIQUE(Table_query__19[Recipient Company])

    and against each

    =TEXTJOIN(", ", 1, IF( Table_query__19[Recipient Company] = D2, Table_query__19[Tracking Number], "") )

     

    2) PivotTable with adding data to data model and using measure

    Traking Numbers:=CONCATENATEX( Table_query__19, Table_query__19[Tracking Number], ", " )

     

    3) Power Query with grouping and Text.Combine as aggregation

    let
    Source = Excel.CurrentWorkbook(){[Name="Table_query__19"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {{"Tracking Number", type text}, {"Recipient Company", type text}}
    ),
    #"Grouped Rows" = Table.Group(
        #"Changed Type",
        {"Recipient Company"},
        {{"Tr Numbers", each Text.Combine( [Tracking Number], ", " ), type nullable text}}
    )
in
    #"Grouped Rows"

Resources