DAX Return Two Columns With Distinct A Column And Concatenated Distinct B Column Values

Hi NTXDallasUser ,

You can achieve this in Power Query.

First you can remove the duplicate rows (where the id and the name are the same).

Then you group by on the id, select Sum for the name column, and when it shows you the error do a little trick based on this blog post's 3rd chapter and replace the List.Sum with Text.Combine and specify the separator.

Here is the full M code with your sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrydPZWitWJVjICcrz8XcFsYyDbyd8JzDYBsl38/Rx9XMBckB4XxzBXOAdugCmQ4+ji6KsUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalespersonNum = _t, SalespersonName = _t]),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"SalespersonNum", Int64.Type}, {"SalespersonName", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SalespersonNum"}, {{"SalespersonS", each Text.Combine([SalespersonName],", "), type nullable text}})
in
    #"Grouped Rows"

 

Let me know if you have any questions.