Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Chris1300
Helper II
Helper II

How to parse SQL query command with Search?

Hello,

 

I have a table in PowerBI, one of the columns is the [SQL_query_command] for auditing purposes. I would like to parse that column to extract the tables names. I think I can do this using the search funtion, but I have a more specific need and need to figure out how to best use it.

 

Assumptions:

1. Assume the [SQL_query_command] has the word "FROM" 

     1a. The [SQL_query_command] can contain multiple "FROM"

2. Assume the length of all possible tables is less than 50 characters

3. I have another table that contains all the possible table names as KEYWORDS.

 

Pseudo Code:

1.  Search [SQL_query_command] for "FROM" then extract 50 characters right of that index. How do I find the 2nd or 3rd "FROM" and get their index? Consolidate all the "FROM"+50 characters into a new calculated column

 

2. This new consolidated calculated column can be used against the other table to find if the KEYWORD exists and return that value. This I have figured out using:  FIRSTNONBLANK(FILTER(VALUES([KEYWORDS]),SEARCH([KEYWORDS],[NEW_CALCULATED_COLUMNS],1,0)),1)

 

My real struggle is how can I find multiple occurences of the word "FROM" and get their respective index? I am flexible to do this in DAX or Power Query.

 

 

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

Have you considered using "FROM" as the string split identifier?

View solution in original post

I was able to do this in power query and use the built in functions for split and extract functions. It involved multiple steps but i think i got it close enuf for it to be similar. 

 

It would be nice for a feature to exist in powerbi to use some kind of RegEx as a parser maybe in the future. Thanks!

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

Have you considered using "FROM" as the string split identifier?

I was able to do this in power query and use the built in functions for split and extract functions. It involved multiple steps but i think i got it close enuf for it to be similar. 

 

It would be nice for a feature to exist in powerbi to use some kind of RegEx as a parser maybe in the future. Thanks!

you can run a regex parser in a Python script inside Power Query.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.