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?