cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
reno1
Frequent Visitor

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?

1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft
Microsoft

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) 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Phil_Seamark
Microsoft
Microsoft

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) 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

@Phil_Seamark, this works perfectly! Thank you

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors