Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
mandix
Regular Visitor

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

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

1Mike
2Alfred
3Kate
4Ethan

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

1Male
2Female
3Professional

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

11
21
32
13
23
41

Here's the basic model. 

mandix_0-1734900135627.png

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

mandix_1-1734900226990.png

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). 

mandix_2-1734900387050.png

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], ", ")
)

 

mandix_3-1734900772607.png

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

Thank you very much. 

Regards, 

Manjo.

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
4 REPLIES 4
mandix
Regular Visitor

mandix_0-1734983947400.pngmandix_1-1734984032069.png

 

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

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. 

Anonymous
Not applicable

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?

vyiruanmsft_0-1734938341575.png

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.