topic Parse Existing Column in DirectQuery mode in Desktop

Parse Existing Column in DirectQuery mode

reno1 — Thu, 16 Mar 2017 04:39:04 GMT

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?

Re: Parse Existing Column in DirectQuery mode

Phil_Seamark — Thu, 16 Mar 2017 07:33:27 GMT

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)

Re: Parse Existing Column in DirectQuery mode

reno1 — Thu, 16 Mar 2017 22:44:01 GMT

Phil_Seamark, this works perfectly! Thank you