Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Return a value from a duplicate

Hello Power BI community, I am seeking help regarding duplicates in my data. I'll take a shot at explaining it but I would like to find a way to display a value based on duplicate serial numbers. Th...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    If so, just adjust the formula as below:

    Order Number 2 =
    CONCATENATEX (
    FILTER (
    SUMMARIZE( 'Table','Table'[Order Number],'Table'[Serial number]),
    'Table'[Serial number] = EARLIER ( 'Table'[Serial number] )
    && 'Table'[Order Number] <> EARLIER ( 'Table'[Order Number] )
    ),
    'Table'[Order Number]
    )

     

     

    Regards,

    Lin

  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    Ok, try this:

    Order Number 2 =
    CONCATENATEX (
    FILTER (
    SUMMARIZE( 'Table','Table'[Order Number],'Table'[Serial number]),
    'Table'[Serial number] = EARLIER ( 'Table'[Serial number] )
    && 'Table'[Order Number] <> EARLIER ( 'Table'[Order Number] )
    ),
    'Table'[Order Number]
    ,
    ","
    )

     

     

    Regards,

    Lin

Anonymous's avatar
Anonymous
Not applicable
6 years ago

A small variant of the solutions already presented.

This function should work  for the more general case of ID n-uplicate, giving as many columns as there are the maximum repetitions of the IDs.

 

let
    rog= (tab as table, grpCols as list, splitCol) => let        
         grp = Table.Group(tab, grpCols, {{"ord", each Table.Column(_, splitCol)},  {"ord2", each Table.Column(_, splitCol)}}),
         tabGrp = Table.ExpandListColumn(grp, "ord"),
         addTab = Table.AddColumn(tabGrp, "addOrd", each try List.Difference([ord2],{[ord]}) otherwise ""),
         transTab = Table.TransformColumns(addTab, {"addOrd", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
         spliTab = Table.SplitColumn(transTab, "addOrd", Splitter.SplitTextByDelimiter(","), {"ord.1", "ord.2"}, ""),
         cleanTab = Table.RemoveColumns(spliTab,{"ord2"})
   in
         cleanTab
in
    rog

 

 

As an example of a call.

 

 

RestOfGroup(data, {"sernum","name"},"ord")