Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
So like this?
TextJoin =
VAR __Table = { [Dog], [Cat], [Pig], [Horse], [Cow], [Sheep] }
RETURN
CONCATENATEX(
FILTER(__Table,[Value]<>""),
[Value],
","
)
PBIX attached.
So like this?
TextJoin =
VAR __Table = { [Dog], [Cat], [Pig], [Horse], [Cow], [Sheep] }
RETURN
CONCATENATEX(
FILTER(__Table,[Value]<>""),
[Value],
","
)
PBIX attached.
Hi @Greg_Deckler great solution,
just a question, is possible count the words in this column?
For exemple:
TextJoin Count
Dog | 1 |
Cat,Sheep | 2 |
0 | |
0 | |
Pig,Horse,Cow | 3 |
Thank you so much.
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.
Thanks Greg! Worked like a charm.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |