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

[jlynch]

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.

[Phil_Seamark]

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

[jlynch]

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.

[MarcelBeug]

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

[MarcelBeug]

This is my interpretation and Power Query solution:

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

:

[jlynch]

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?

[MarcelBeug]

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:

[jlynch]

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!!!!

[chandrasekhar1]

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

[hemantsingh]

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 · Accepted solution]

I believe what you want is a VALUES and CONCATENATEX formula