DAX for a dynamic number of columns that have 95% the same name

[remi]

Hello,

Want to concatenate only the columns that start with Column.

e.g. 3 out of 140 columns that start with Column, but they change in the name  like Coumn_1; Column_7; Column_21

Can you help me with the dax for this? 

Thank you!

[v-juanli-msft]

Hi @remi

How do you concatenate the columns?

Is it like below?

Original table

Final table

Here is my pbix

Best Regards

Maggie

[remi]

hello @v-juanli-msft,

Created a new column with the merged content in the query view and then moved to a new formula to check for my text in the table view.

[v-juanli-msft · Accepted solution]

Hi @remi

It seems my pbix provides the same way as you described, could you have a look at it and tell me whether it helps or not?

Best Regards

maggie

[Stachu]

AFAIK in DAX whenever you reference a column it has to be explicit reference, there is no equivalent of Excel INDIRECT, so in DAX you need to write the code

what you could try is unpivoting these columns in Query Editor so they would be all in one column with values in the other one and then using CONCATENATEX with criteria on the column with names stored - not sure it applies to your logic though