How to parse SQL query command with Search?

[Chris1300]

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.

[lbendlin · Accepted solution]

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

[lbendlin · Accepted solution]

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

[Chris1300 · Accepted solution]

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!

[lbendlin]

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