March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to Solution.
I believe what you want is a VALUES and CONCATENATEX formula
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. 🙂
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.
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
:
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:
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
I believe what you want is a VALUES and CONCATENATEX formula
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
89 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |