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

[kamran]

Hi folks

(Appologies for also posting it to wrong forum "Report Server" a while before)

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 here or any other suggestion?

[v-huizhn-msft]

Hi @kamran,

You should share some sample data and list expected result, so that we can help you in details. You can create some fake data if your data is private.

Best Regards,
Angelia

[kamran]

Thanks v-huizhn for your attention, Here is the sample data:

In 2017, a XYZ appointed external moderator post-moderated a large
number of assessments of the undergraduate programmes. While the overall
outcome of the post-moderation was “met” requirements, the moderator
highlighted some areas of concern that needed to be addressed urgently. The
academic team has worked closely with the School’s Management, and the
ABC and DEF combined Academic Directorate to address all issues. 

Overall, student achievement rates are excellent.

In this data, line breaks after "large" in first line, or "overall" in 2nd line or similarly in the next lines, are not acceptable. However line-break after "issues." in the 2nd last line is fine because there is a full stop (.) after the word "issues".

My below mentioned formula works blindly as it creates line-breaks after each Full Stop or the other way, it removes all line-breaks.

[v-huizhn-msft]

Hi @kamran,

Please review the following ideas and vote them.

break line where dates have no data
Data Transformation
Ability to indicate filtered data by breaking line in line chart

Best Regards,
Angelia

[kamran]

Hi gurus...is there any solution to my below problem??