Return Non-Blank Values From Multiple Columns With Delimiters

[Anonymous]

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.

[Greg_Deckler · Accepted solution]

So like this?

TextJoin = 
  VAR __Table = { [Dog], [Cat], [Pig], [Horse], [Cow], [Sheep] }
RETURN
  CONCATENATEX(
      FILTER(__Table,[Value]<>""),
      [Value],
      ","
  )

PBIX attached.

[roger_rdf]

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.

[TaufikMaggangka]

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.

[Anonymous]

Thanks Greg! Worked like a charm.