Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

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

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?

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.