Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Concatenate Distinct Text order by id

Hi community,

 

I have a table with two columns as shown below.

 

 

I would like to concatenate the distinct Product values considering the products with higher ID (id DESC). Separated by comma, same as the example below

 

Smartphone,Cable,Tshirt,TV

 

File is HERE.

 

Thanks,

Bruno Monteiro

Need Help

3 Replies

  • adudani's avatar
    adudani
    Icon for Memorable Member rankMemorable Member
    3 years ago

    hi Anonymous ,

     

    have solved in power query:

     

    let
    Source = Csv.Document(File.Contents("C:\Users\Avi\OneDrive\Desktop\nba data\data.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text}, {"id", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Max Id", each List.Max([id]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Max Id", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Max Id"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
    in
    #"Merged Columns"

     

     

    Please mark this solution as accepted if it resolves the query.

     

    Appreciate a thumbs up if this helped.

    • adudani's avatar
      adudani
      Icon for Memorable Member rankMemorable Member
      3 years ago

      Alternatively with DAX:

       

      Step1.

      Create a table: 

      Table 2 = SUMMARIZE(Tabela1,Tabela1[Product] ,"MAX ID",MAX(Tabela1[id]))
       
      Step 2.
      Create a measure:
      Concat = CONCATENATEX('Table 2','Table 2'[Product],",",'Table 2'[MAX ID],DESC)