Forum Discussion
How to audit datasets in a workspace for Native SQL usage vs direct table connection
Hii Frankllina
Power BI Service does not expose Power Query (M) code, so you cannot directly audit Native SQL vs direct table connections from a workspace UI. The only accurate method is to use the XMLA endpoint (Premium/PPU required) or REST API + Tabular Object Model (TOM) to extract each dataset’s M expressions (partitions/data sources) and programmatically scan for patterns like Value.NativeQuery or hard-coded SQL. If the M code shows direct navigation (e.g., Sql.Database(...){[Schema=...,Item=...]}), it’s direct table/view access. There is no built-in report in Power BI Service that provides this classification.
Hey Frankllina ,
What you’re trying to build is absolutely possible, but it cannot be done purely with DAX inside Power BI Service. The reason DAX Studio works locally is because it connects directly to the XMLA endpoint of a semantic model and lets you inspect metadata. In the Service, you don’t automatically get access to that internal M (Power Query) code unless you explicitly connect through the XMLA endpoint or use the REST APIs.
In simple terms, DAX cannot tell you whether a dataset uses `Value.NativeQuery` or direct table navigation. That information lives in the Power Query (M) definition of the dataset, not in the tabular model layer that DAX sees.
The correct approach is to use the XMLA endpoint for the workspace. If your workspace is on Premium, PPU, or Fabric capacity, you can connect to the XMLA endpoint using tools like Tabular Editor, SSMS, or even Power BI Desktop (Analysis Services connection). From there, you can extract the Model > Expressions metadata, which contains the full M queries. Inside that M code, you can search for patterns like `Value.NativeQuery`, `Odbc.Query`, or hard-coded SQL statements. If those exist, it indicates native SQL usage. If the query follows navigation steps like `Source{[Schema="dbo",Item="Table"]}[Data]`, then it is using direct table/view access.
To automate this for all datasets in a workspace, the scalable solution is to use the Power BI REST API combined with the XMLA endpoint. First, use the REST API to list all datasets in the workspace. Then, programmatically connect to each dataset’s XMLA endpoint and extract its M expressions. You can store that metadata in a control dataset and build your governance dashboard on top of it. This is how enterprise governance solutions typically audit semantic models.
If your workspace is not on Premium/Fabric capacity, you won’t have XMLA read access. In that case, there is no supported way to centrally inspect M queries from the Service. You would need either Premium capacity or a governance process that scans PBIP projects in source control (if you’re using Git integration).
So the clean enterprise solution is: use XMLA endpoint + REST API to extract M expressions → classify native SQL vs direct table navigation → store results in a governance dataset → build your control dashboard on top of that.
If this explanation helped, please mark it as the solution so others can find it easily.
If it helped, a quick Kudos is always appreciated it highlights useful answers for the community.
Thanks for being part of the discussion!