Forum Discussion

AnjanaPothineni's avatar
AnjanaPothineni
Frequent Visitor
3 years ago

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

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

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      AnjanaPothineni
      Frequent Visitor
      3 years ago

      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.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    3 years ago

    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"