How to extract a table name from a query string in DAX

[AnjanaPothineni]

Hi, I have a Power BI report to list all the queries that are ran as part of query auduting and I have a column that contains a query.

In order to simplify the data I need to split (extract) the table name from the query  string I have.

Please help me with the DAX for this.

Sample query is : 
SELECT
AST.SERIALNUMBER,
AST.PRODUCTID,
EOSL = max(EOL.NEXTENDOFLIFEMILESTONEDATE)
FROM [HSTG].[HSTG_ASSETS] AST
INNER JOIN [HSTG].[HSTG_HARDWARE_END_OF_LIFE] EOL ON AST.DEVICEID = EOL.DEVICEID
WHERE NEXTENDOFLIF

From the above I need to display FROM table names like '[HSTG].[HSTG_ASSETS]' & [HSTG].[HSTG_HARDWARE_END_OF_LIFE] 

Please help

[danextian]

Hi @AnjanaPothineni ,

I would do this in Power Query.

Frist split the query string by space. This will create column containing a list  of text strings.

Next is to select which of the text strings begings with [ and ends with ].

Lastly, you can either expand the list as new rows or be concatenated in a single cell.

Paste this code into a blank query. I split the logic into several applied steps/column so it is easier to understand.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5BCsIwEEWvMnSlILmBi9j82kiakUyqQhOCBzBrj2+qCLoa/vDnzVuWTuDQx1S1RCUIVjs/TweE3Wd1DmzmPlrTMlgc7elxf27ATnncIrzhwdkBk3WQyB5GR2xTHQJPtIwSj1m9R9EiiJKpUVO13iPQia3/L406mKsOKA1ceCgrOlP7RuzXS2VwsT2saR6rwzemeh0RQL9OqaZKXc4v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sample query" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sample query", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Split by Space", each Text.Split ( [Sample query], " " )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Select Start and Square Brackets", each List.Select([Split by Space], each Text.StartsWith(_, "[") and Text.EndsWith(_, "]") )),
    #"Expanded Select Start and Square Brackets" = Table.ExpandListColumn(#"Added Custom1", "Select Start and Square Brackets"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Select Start and Square Brackets",{"Split by Space"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Select Start and Square Brackets", type text}})
in
    #"Changed Type1"

[grazitti_sapna]

Hi @AnjanaPothineni 

You can try using this for achieving your desired output.

TableNames =
VAR StartIndex = FIND("[", 'YourTable'[Query], 1)
VAR EndIndex = FIND("]", 'YourTable'[Query], StartIndex)
RETURN
MID('YourTable'[Query], StartIndex, EndIndex - StartIndex + 1)

Hope this will helo you.

[AnjanaPothineni]

Hi @grazitti_sapna 

Thanks for quick reply. Logic seems to work but my query has all sorts of table referecing is tere like sometimes they used schema with table name with[] and in some its a query with just table name etc. I will refine it further and apply this logic there.