Parse Existing Column in DirectQuery mode

[reno1]

Hi,

I'm working with a table that, in one column, contains different strings of text that I need to extract data from. Some of these strings include:

• "Matched with confidence: 100%"
• "Connection Error 566-353-54354 - User busy"
• "Matched with confidence: 100%. Variables: Step1Choice1=Hello"
• "No match. Confidence: 59% is less than the required 80%"

NOTE: The bold underlined number is what I need.

Basically, if a number is proceeded by "onfidence: " and followed by "%" I need to add it to its own column. I found a couple of ways to achieve this, but the result was I could no longer use a live connection with DirectQuery. As my database is beyond massive, import mode is not an option. Also due to circumstances, I am unable to parse in the database before connecting to Power BI. 

So, is there a way I can parse the required data from the column while staying in DirectQuery mode (possibly using a DAX query)? Or am I out of luck?

[Phil_Seamark · Accepted solution]

Hi @reno1

Are you able to add calcualted columns to your tables?  If so this might be close

Parsed Column = 
var myStart = FIND("onfidence:",'Table3'[Text],,0)
var myLength= FIND("%",Table3[Text],myStart + 11,0) - (myStart + 11)
var myResult = IF(myStart>0,MID('Table3'[Text],myStart + 11,myLength),Blank())
RETURN int(myResult) 

[reno1]

@Phil_Seamark, this works perfectly! Thank you