How to get rid of line breaks in text

[ITManuel]

Hi,

I have a text in an excel file with several line breaks in it. I would like to remove all line breaks to save space.

This is the text in the excel file:

I'm trying this code to identify line breaks, replace them with a space and then remove the spaces with trim.

VAR RemoveLineBreak =
  SUBSTITUTE ( RawText, UNICHAR ( 10 ), " " )
VAR TrimText =
  TRIM ( RemoveLineBreak )
RETURN
TrimText

 The code works to identifiy and replace the line breaks. If "A" is used instead of " " in the SUBSTITUTE function any line break is replace by an "A".

but the result contains still all the line breaks.

The strange thing is that if I replace the text in the source (excel file) while keeping all the line breaks, all line breaks are removed. 

With the same code as above:

The excel file is updated via a Microsoft Query call from the ERP system. It seems that there is something in the source data which prevents the code from working. 

Best regards

[v-henryk-mstf]

Hi @ITManuel ,

If the data provider has the latest news, please reply to the repaired result. If the problem persists, please point out.

Looking forward to your reply.

Best Regards,
Henry

[ITManuel]

Hi @v-henryk-mstf ,

i couldn't speak to the data provider so far, but I've seen in the meantime that the problem actually only appears in DaxStudio but not in the PBi report (with the same code).

This is the result of DaxStudio:

At the same time, this is the result in the PBi report:

So in the report visual itself the removal of line breaks works. When hovering over it, the appearing window still contains line breaks as the raw data and the result of DaxStudio.

When using tooltip, the tooltip does neither contain any line breaks.

I don't have any idea why, but importantly it works in the report. 🙂

Best regards

[TomMartens · Accepted solution]

Hey @ITManuel , 

your formula works, as you can see here, my DAX is essential the same as yours:

using replace = 
SUBSTITUTE( 'Table'[Text] , UNICHAR( 10 ) , " " )

I used SHIFT+ENTER to create the soft breaks / new lines.

And here is my simple table visual:

Maybe, the new line is a little bit different, and Excel is maybe a little smarter.

Maybe this article: https://en.m.wikipedia.org/wiki/Newline

provides some insights, and you may try other decimal values than 10.

Hopefully, this provides some insights to tackle your challenge.

Regards,

Tom

[ITManuel]

Hi @TomMartens ,

thank you for your response. So far I wasn't able to identify the issue, I will try to speak to the data provider to see weather he has any idea.

Best regards