Concatenate Distinct Text order by id

[Anonymous]

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

[CNENFRNL · Accepted solution]

[adudani]

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]

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)