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?
