Forum Discussion
How to access M queries when Power BI Admin API returns empty tables array for PbixInImportMode data
I'm using the Power BI Admin API to scan our workspaces and extract data sources and M queries from datasets.
Specifically, I'm using the /v1.0/myorg/admin/workspaces/getInfo endpoint with the following parameters set to true: datasourceDetails, datasetSchema, and datasetExpressions.
The issue I'm facing is that for all datasets with contentProviderType: "PbixInImportMode", the API returns empty tables arrays ("tables": []).
This makes it impossible to access the M queries which would normally be found in the table.expressions property.
Here's a sample of the API response for one of our datasets:
{ "id": "1eac801f-acd4-4217-a9dc-b7a3fbf98b5f", "name": "Dataset Name", "tables": [], "configuredBy": "[email protected]", "configuredById": "98969bd4-047a-4c50-aa24-d5357737e907", "isEffectiveIdentityRequired": false, "isEffectiveIdentityRolesRequired": false, "targetStorageMode": "Abf", "createdDate": "2022-07-11T13:02:43.403", "contentProviderType": "PbixInImportMode" }
I need to access the M queries for our data governance and documentation purposes. Is there any way to extract M queries from PbixInImportMode datasets when the tables array is empty? Are there any alternative API endpoints or parameters I should use? Any help would be greatly appreciated.
Hi S_Kostiantyn ,
You cannot reliably retrieve M queries from “PbixInImportMode” datasets using the Admin getInfo endpoint because it often returns empty tables arrays for import‐mode PBIX files. Instead, you can use the Metadata Scanning (Lineage) APIs that allow you to initiate and retrieve a tenant‐wide scan, returning dataset schema and M expressions for each dataset. Alternatively, if the dataset is in Premium or Premium Per User capacity, you can connect via the XMLA endpoint using a tool such as SQL Server Management Studio or Tabular Editor to inspect the Tabular Object Model and locate the M queries under each table’s partition. For example, in SSMS you can run the following query to retrieve the M expressions:
SELECT * FROM $SYSTEM.TMSCHEMA_PARTITIONSThe export to PBIX option is another possibility, though less ideal for automation or large‐scale governance.
Best regards,
5 Replies
Hi S_Kostiantyn ,
You cannot reliably retrieve M queries from “PbixInImportMode” datasets using the Admin getInfo endpoint because it often returns empty tables arrays for import‐mode PBIX files. Instead, you can use the Metadata Scanning (Lineage) APIs that allow you to initiate and retrieve a tenant‐wide scan, returning dataset schema and M expressions for each dataset. Alternatively, if the dataset is in Premium or Premium Per User capacity, you can connect via the XMLA endpoint using a tool such as SQL Server Management Studio or Tabular Editor to inspect the Tabular Object Model and locate the M queries under each table’s partition. For example, in SSMS you can run the following query to retrieve the M expressions:
SELECT * FROM $SYSTEM.TMSCHEMA_PARTITIONSThe export to PBIX option is another possibility, though less ideal for automation or large‐scale governance.
Best regards,
Thank you for the detailed explanation and assistance.
I'd like to clarify if I'm understanding the concept of lineage correctly and if my API request is properly constructed. I've tried using the Lineage API as you suggested with this approach:
1. Get list of workspaces
2. Send POST request to /v1.0/myorg/admin/workspaces/getInfo with parameters: { "workspaces": [workspace_ids], "lineage": true, "datasourceDetails": true, "datasetSchema": true, "datasetExpressions": true } 3. Check scan status and retrieve results
While this successfully returns a JSON response, I can't find any M queries in the results. If I'm understanding correctly, this approach doesn't work for my use case despite having a successful scan.
Also, I'd appreciate if you could comment on using the Microsoft Graph operators from Apache Airflow (https://airflow.apache.org/docs/apache-airflow-providers-microsoft-azure/stable/operators/msgraph.html) for this purpose. Would these operators be capable of retrieving Power BI reports and their data sources, particularly the M queries?
Thanks again for your help.
Hi S_Kostiantyn ,
Thanks for reaching out to Microsoft Fabric Community Forum.The method using the Lineage API (/v1.0/myorg/admin/workspaces/getInfo) is appropriate for fetching workspace metadata. However, it does not consistently return M queries for datasets that are in Import mode (specifically, those in PbixInImportMode).
Regarding Microsoft Graph Operators :
Utilizing Airflow Microsoft Graph Operators enables you to execute REST calls to the Microsoft Graph API. This allows for seamless integration with various Microsoft services, such as Power BI, SharePoint, etc., within your Airflow workflows.
It can used for Managing Power BI reports (e.g., refreshing datasets) Retrieving dataset metadata (like data sources).To effectively extract M queries for datasets in Import Mode, the most suitable approach is to use XMLA endpoints.
If our response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.
Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!
