Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kamran
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?
0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.