Forum Discussion

reno1's avatar
reno1
Frequent Visitor
9 years ago
Solved

Parse Existing Column in DirectQuery mode

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?

Need Help
Tips and Tricks
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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)

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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)