Trying to convert rows to comma separated values where a bridge table is involved.

[mandix]

Hi all, 

I have two dimension tables joined via a bridge table. I want to concatenate all possible values using a comma and present them against the other dimension primary key values.

I have a .pbix file but it's not allowing me to attach for reference. To help explain here's comes some preparation. 

Preparation:

1. CustQ =

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

2. CustQ Data =

CustKeyCustName

1	Mike
2	Alfred
3	Kate
4	Ethan

3. CatQ =

let
    KeyedIn = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNzElVitWJVjICctxSc2FcYyA3oCg/LbW4ODM/LzFHKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CatKey = _t, Cat = _t]),
    #"Changed Type" = Table.TransformColumnTypes(KeyedIn,{{"CatKey", Int64.Type}})
in
    #"Changed Type"

4. CatQ Data = 

CatKeyCat

1	Male
2	Female
3	Professional

5. BridgeCustCatQ =

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

6. BridgeCustCatQ Data = 

CustKeyCatKey

1	1
2	1
3	2
1	3
2	3
4	1

Here's the basic model. 

What I have achieved so far is as per below screen shot. 

Below is the code for the right hand table. 

LISTAGG_CatKey = SUMMARIZECOLUMNS(
    CustQ[CustKey],
    TREATAS(VALUES(CustQ[CustKey]), BridgeCustCatQ[CatKey]),
    "CatKeys", CONCATENATEX(VALUES(BridgeCustCatQ[CatKey]), BridgeCustCatQ[CatKey], ", ")
)

Issue:

But ideally what I need is highlighted below (contents of the screen shot, overall, are incorrect as far as "Cat" values are concerned). 

Below is the code I have attempted for the actual category names (but currently returning all category names - which is not expected). 

Table 2 = SUMMARIZECOLUMNS(
    CustQ[CustKey],
    TREATAS (VALUES(CatQ[CatKey]), BridgeCustCatQ[CatKey]),
    "Cat", CONCATENATEX(VALUES(CatQ[Cat]), CatQ[Cat], ", ")
)

I tried concatenating the category keys. However, the following code is also not giving an expected result. 

EVALUATE
SUMMARIZECOLUMNS(
    CustQ[CustKey],
    TREATAS (VALUES(CustQ[CustKey]), BridgeCustCatQ[CatKey]),
    "CatKeys", CONCATENATEX(VALUES(CatQ[CatKey]), CatQ[CatKey], ", ")
)

Not sure, what I am doing wrong. Can someone please help? 

Thank you very much. 

Regards, 

Manjo.

[mandix]

LISTAGG_Cat = SUMMARIZECOLUMNS(
    CustQ[CustKey],
    "Cat", CONCATENATEX(VALUES(CatQ[Cat]), CatQ[Cat], ", ")
)

[mandix]

The forum suggested a post (it's not being shown now) where it was mentioned that bridge tables need bidirectional support. I changed it and I am getting the expected results now. 

[v-yiruan-msft]

Hi @mandix ,

Thank you for reaching out to us and for updating us on your progress. It's glad to hear that you were able to identify and resolve the issue on your own. Could you please mark your post as Answered since it has been resolved by yourself?

If you have any further questions or need additional assistance in the future, please don't hesitate to reach out. Your proactive approach and willingness to share your solution are greatly appreciated and can be very helpful to other community members facing similar challenges. Thank you.

Best Regards

[mandix · Accepted solution]

Got the link now which helped. 

https://community.fabric.microsoft.com/t5/Desktop/Bridge-Table/td-p/3724666