How to access M queries when Power BI Admin API returns empty tables array for PbixInImportMode data

[S_Kostiantyn]

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": "user@domain.com", "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.

[DataNinja777 · Accepted solution]

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_PARTITIONS

The export to PBIX option is another possibility, though less ideal for automation or large‐scale governance.

Best regards,

[S_Kostiantyn]

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.ht...) 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.

[v-aatheeque]

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!
 

[S_Kostiantyn]

Hello!

Thank you very much for your detailed explanation and assistance!

I'd like to clarify regarding Microsoft Graph API: is it possible to use it to access data sources and M queries in Power BI reports?

In my current situation, using XMLA endpoints isn't optimal because I'm planning to integrate Power BI with Apache Airflow DAGs running on Linux. My Airflow environment is already running in Docker containers, which makes working with Windows-specific MSOLAP drivers particularly challenging.

I need a solution that works directly through API calls that can be installed via packages rather than requiring full software installations. Since I plan to integrate Power BI with Apache Airflow DAGs, I want to extract data sources from Power BI to create relationships within my data pipelines.

I'm looking for a way to programmatically access M queries from Power BI datasets through my Airflow pipelines. If Microsoft Graph API can provide this capability, or if there are other API-based approaches that can be implemented with standard Python packages in a Linux environment, that would be tremendously helpful.

Thank you again for your assistance!

[v-aatheeque]

Hi @S_Kostiantyn 

Currently, there is no direct API-based method for programmatically extracting M queries through standard Python packages.I recommend submitting the detailed feedback and ideas through Microsoft's official feedback channels, such as the Microsoft Fabric Ideas.  . Feedback submitted through these channels is frequently reviewed by the p...

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.