SQL/PowerBI search in a particular line (not row) of data

I have a column with multiple line data. It shows as one line in SQL Server but multiple lines in Power BI so I guess there are some special characters in there to tell Power BI where the new line starts.

What I want to achieve is to search the second to the last line instead the whole cell, to find out whether it contains for example the key word “christchurch” in it.

Is there anyway that I can achieve this in SQL Server or Poewr BI? Thanks.

Answer

In power BI:

Contains = 
var index = 
    SEARCH(UNICHAR(13), [YourColumn], , 0) + 1 
RETURN 
    SEARCH("christchurch", [YourColumn], index, 0)

This measure will return the starting index of the word “christchurch” (searches only after first newline char) and return 0 if not found.

Note: You might also want to try UNICHAR(10) or other special characters that might be used for a line break in your data.