How to remove blank fields from concatenated column - DAX

[Betty888]

Hi Powre BI community,

I need your help for a DAX question : I’m trying to concatenate multiple columns containing text and I want to ignore if the field is empty in one of the columns to be concatenated.

For that I use the following query :

Concatenated col = IF(ISBLANK ('TABLE'[Col1]),BLANK(),'TABLE'[Col1]) & UNICHAR(10) & IF(ISBLANK ('TABLE'[Col2]),BLANK(),'TABLE'[Col2]) & UNICHAR(10) & IF(ISBLANK ('TABLE'[Col3]),BLANK(),'TABLE'[Col3]) & UNICHAR(10) & IF(ISBLANK ('TABLE'[Col4]),BLANK(),'TABLE'[Col4]) & UNICHAR(10) & IF(ISBLANK ('TABLE'[Col5]),BLANK(),'TABLE’[Col5])

The issue is that this gives the following view :

And my objective is removing the empty fields.

Can anyone help me to achieve that please 🙏

[vicky_ · Accepted solution]

Move the UNICHAR(10) inside the IF statements so you will only get a new line if there's a new line that isn't blank

[Betty888]

Dear @vicky_ ,

Just want to confirm that the solution you suggested works perfectly, indeed, for the remaining 6 columns , it was a mistake in the formula I've used to create them where a space has to be replaced by BLANK, now everything works perfectly.

Many thanks again ! 

[Betty888]

Very many thanks for your help ! 

I included the separator in the IF statement, but something strange happened : it works for the firts 6 columns, for all the rest it didn't work even that I'm using the same statement, I'm just changing the column names!!!! In my concatenated column, I'm combining 12 columns in total