DISTINCT for Grouped ROWS in POWER BI (DAX)

Question

I had a TABLE

ID TYPES 1 A \\ 1 B \\ 2 B \\ 3 A \\ 4 A \\ 4 A \\ 4 A \\ 4 C \\ 4 D \\ 4 E \\ 5 B \\ 5 B \\ 6 A \\ 7 A \\ 7 B \\ 7 C \\ 8 B \\ 8 B \\ 9 D \\ 10 A \\ 10 A \\ 10 D

I have TABLE:

ID TYPES
1 A+B \\ 2 B \\ 3 A \\ 4 A+A+A+C+D+E \\ 5 B+B \\ 6 A \\ 7 A+B+C \\ 8 B+B \\ 9 D \\ 10 A+A+D

It was used: let Source = Excel.Workbook(File.Contents("c:\Desktop\stac.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"TYPE", type text}, {"ID", type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"ID", type text}, {"TYPE", type text}}), #"Grouped Rows1" = Table.Group(#"Changed Type", {"ID"}, {{"All Rows", each , type table [ID=text, TYPE=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each [All Rows][TYPE]), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(, Text.From), "+"), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"All Rows"}) in #"Removed Columns"

But I need to distinct values:

ID TYPES
1 A+B \\ 2 B \\ 3 A \\ 4 A+C+D+E \\ 5 B \\ 6 A \\ 7 A+B+C \\ 8 B \\ 9 D \\ 10 A+D

Answer 1

As first step group your table in the query designer by ID and Types. So your table would become from this

ID   Types
1     A         
1     B  
1     B         
2     B         
3     A        
4     A        
4     A         
4     A          

to this:

ID   Types
1     A         
1     B         
2     B         
3     A                
4     A   

Then apply the same step as you did in your code above to combine the different types in one column:

Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(, Text.From), "+"), type text})