Forum Discussion
Return Non-Blank Values From Multiple Columns With Delimiters
I have the following table:
ID | Dog | Cat | Pig | Horse | Cow | Sheep |
1 | Dog | |||||
| 2 | Cat | Sheep | ||||
| 3 | ||||||
| 4 | Pig | Horse | Cow |
I'm trying to create a calculated column that would populate a string based on the non-blank values of EACH of the six columns with a ", " delimiter. The results from above would be:
| ID | Animals |
| 1 | Dog |
| 2 | Cat, Sheep |
| 3 | None |
| 4 | Pig, Horse, Cow |
I've tried doing nested CONCATENATE and IF statements, but the string separation is the most difficult part. Essentially, I'm looking for something akin to TEXTJOIN from Excel. Anyone point me in the right direction?
This is a created table within my model, so I unfortuantely can't use any of the M table.join functions to help.
So like this?
TextJoin = VAR __Table = { [Dog], [Cat], [Pig], [Horse], [Cow], [Sheep] } RETURN CONCATENATEX( FILTER(__Table,[Value]<>""), [Value], "," )PBIX attached.
4 Replies
- Greg_Deckler
Community Champion
So like this?
TextJoin = VAR __Table = { [Dog], [Cat], [Pig], [Horse], [Cow], [Sheep] } RETURN CONCATENATEX( FILTER(__Table,[Value]<>""), [Value], "," )PBIX attached.
TaufikMaggangkaHelper II
Hi Greg_Deckler ,
I have similiar issues here, the different is I am using direct query mode and "concanatex" is not support direct query.
it is possible to it in "Measure" ? your support will be really appreciated.
Tahnk you.Hi Greg_Deckler great solution,
just a question, is possible count the words in this column?
For exemple:
TextJoin CountDog 1 Cat,Sheep 2 0 0 Pig,Horse,Cow 3 Thank you so much.
