Retrieve All related data from another table as a list

[timazarj]

Hello,

In the model there 2 tables one for customer information and the other one for the product they bought:

Customer:

ID	Name
1	Brian
2	Tammy

Product

ID	Product
1	A
1	B
1	C
1	D
2	A
2	F

I tried to add a new column in the customer table listing all the products: 

ID	Name	Product List
1	Brian	A,B,C,D
2	Tammy	A,F

Thanks,

[timazarj]

Thank you, 

Do you have any idea how can I define it as a measure using DAX.

[V-lianl-msft · Accepted solution]

Hi @timazarj ,

Please try CONCATENATEX function:

Measure = CONCATENATEX('Table (2)','Table (2)'[Product],",")

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[freginier]

Create these queries in power query

Product Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzgLGc4ywXMMoKrA7HclGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Product", type text}})
in
    #"Changed Type"

Customer Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIqykzMU4rViVYyAvJCEnNzK5ViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, Product, {"ID"}, "Product", JoinKind.LeftOuter),
    #"Expanded Product" = Table.ExpandTableColumn(#"Merged Queries", "Product", {"Product"}, {"Product.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Product",{{"Product.1", "Product"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"ID", "Name"}, {{"Product", each Text.Combine([Product],","), type nullable text}})
in
    #"Grouped Rows"

You should get this