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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jlynch
Frequent Visitor

calculated column to display comma separated values as the text lookup value for each value

I have two tables, one lists the text string values and an ID, the other has a comma separated list of ID's, i want to display a concatenated text list in a single cell based on the ID's in the main table, so:

 

Table 1

ID        Text Value

 

Table 2

Lookup value to Table 1 = comma separated IDs

 

what we need is comma separated text values.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

I believe what you want is a VALUES and CONCATENATEX formula


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

10 REPLIES 10
Phil_Seamark
Employee
Employee

Hi @jlynch

 

I think I follow what you need, but just to be sure, can you please mock up a small sample of fake data for your two tables including your expected result.  We can try and help you with the middle part. 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Marcel below has interpretted what i need accurately, I'm going to attempt his solution but it's a bit more of a code solution than I'm used to.

Coincidentally I just recorded a video for another question in which I demonstrate the same technique for concatenating the text values.

Specializing in Power Query Formula Language (M)

This is my interpretation and Power Query solution:

 

Calculated column to display CSTVs.png

 

CSTV query code:

 

let
    Source = Table2,

    // Get nested lists of ID numbers
    SplitToIDNumbers = Table.TransformColumns(Source,{},each List.Transform(Text.Split(_,","),Number.From)),

    // Add index as a grouping for the IDs after expansion
    AddedIndex = Table.AddIndexColumn(SplitToIDNumbers, "Index", 0, 1),
    ExpandedIDs = Table.ExpandListColumn(AddedIndex, "IDs"),

    // Get the Text Values from table1
    MergedWithTable1 = Table.NestedJoin(ExpandedIDs,{"IDs"},Table1,{"ID"},"NewColumn",JoinKind.LeftOuter),

    // Expand the nested tables
    ExpandedTextValues = Table.ExpandTableColumn(MergedWithTable1, "NewColumn", {"Text Value"}, {"Text Value"}),

    // Use Transform - Group By to create base code (e.g. use group function "Sum"  for the text values, then adjust the code to Text.Combine
    Grouped = Table.Group(ExpandedTextValues, {"Index"}, {{"Text Values", each Text.Combine([Text Value],","), type text}}),

    RemovedIndex = Table.RemoveColumns(Grouped,{"Index"})
in
    RemovedIndex

:

 

Specializing in Power Query Formula Language (M)

Marcel...

the example is perfect, the solutions I'm not sure of, I'm in Power BI, I don't know M so I'm not sure, do I go to the Query Editor and there enter the formulas you indicated, is this a measure or a column?  Sorry to be a bit slow on this one, but I think the process is just beyond my skill level?

It is a Power Query (M) solution in which a new table is created.

 

If you are not familiair with Power Query, then you might prefer the DAX solution from @Greg_Deckler.

 

The Power Query solution requires several adjustments of generated code, as demonstrated in this video.

At the end of the video I show how to copy/paste the code from my previous post.

 

Edit: the video doesn't show how to go into the advanced editor after creating a blank query to paste the copied code:

 

Goto Advanced Editor.png

 

Specializing in Power Query Formula Language (M)

it's not fair to say one answer was correct here, everyone helped me get to the solution, but i marked the solution since that is the direction I went.  Marcel has propelled me to take my limited knowledge of power query to the next level for the next challenge,

 

Thanks to everyone!!!!

What if the Source data is like below:

 

ID     Value

__      ________

1             X

1             Y

2             Z

2             A

2             B

3           

4             C

5             D

6             E

6             F

 

and My output should be 

 

ID          Value

-----       ---------

1               X,Y

2               Z,A,B

3

4              C

5              D

6              E,F

 

 

Please let me know.

 

Thanks

Chandra

hi @chandrasekhar1,

 

     Did you get any way out or resolution for your posted issue. I too want to create a id column that has comma separated value just like you asked. Let me know if you have resolved it.

 

Regards,

Hemant

Greg_Deckler
Super User
Super User

I believe what you want is a VALUES and CONCATENATEX formula


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors