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 dep...
excel
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