Conditionally Replace Line-Breaks with Space within the same multi-line text field

kamran — Tue, 17 Jul 2018 09:32:09 GMT

Hi folks
 
I have a multi-line text column (equivalent to nVarchar (max) ) which is getting data from SharePoint using OData connection.
So it's limiting me to apply easy SQL on data.
Each field of this text column is having multi-line comments from end users.
 
I've tried to manipulate it using a Conditional Column in Data Model of Power BI using the below formula:
 
T_Comments = IF((find(UNICHAR(10),KeyEvaluationQuestions[ExecutiveSummary],1)+1) = find(UNICHAR(46),KeyEvaluationQuestions[ExecutiveSummary],1), KeyEvaluationQuestions[ExecutiveSummary], SUBSTITUTE(KeyEvaluationQuestions[ExecutiveSummary], UNICHAR(10)," "))
My logic was to replace all the line breaks (UNICHAR(10)) with a Space, which are not followed by a Full-Stop (UNICHAR(46)). Otherwise, it should not change the feild values.
 
But, I think, whenever the condition is true, this formula (
SUBSTITUTE(KeyEvaluationQuestions[ExecutiveSummary], UNICHAR(10)," ")
) is working on the whole one field, not replacing the line-breaks conditionally.
 
Logically it makes sense and I'm feeling the need of Loop kind of functionality in Power BI DAX,
which should iterate through the all contents of a field and work conditionally only where required.
 
Can you please suggest me what DAX function can I use for this purpose or any other suggestion?

topic Conditionally Replace Line-Breaks with Space within the same multi-line text field in Report Server

Conditionally Replace Line-Breaks with Space within the same multi-line text field