How Can I Programmatically Read Power Query Dataflow Metadata?

Microsoft Power Query is an extraordinarily powerful tool for data transformation and connectivity across various sources. One of its key features is Dataflows, which allow users to design and manage data transformations in a reusable manner. 

While Power Query provides an intuitive user interface for creating and managing dataflows, there may be scenarios where you need to programmatically access the metadata of these dataflows. In this article, we’ll take a look on how you can achieve this using Power Query and related technologies.

How Can I Programmatically Read Power Query Dataflow Metadata

Methods for Programmatically Accessing Power Query Dataflow Metadata

To programmatically read Power Query dataflow metadata, we can leverage the capabilities of tools such as Power BI REST API and the Power Query M language.

Extract Dataflow Metadata using Power BI REST API

Before accessing dataflow metadata, you need to authenticate with the Power BI service. This can be done using OAuth 2.0 authentication, and it requires registering an application in the Azure portal to obtain client credentials.

Step 1: Registering an Application in Azure Portal

To authenticate with the Power BI service programmatically, you need to register an application in the Azure portal. Follow these steps to do so.

1. Go to the Azure portal and sign in with your Azure account.

2. In the left navigation pane, select Azure Active Directory. 

Registering an Application in Azure Portal

Under App registrations, choose New registration.

Azure account
select Azure Active Directory

Provide a name for your application, select the appropriate Supported account types (usually, “Accounts in this organizational directory only” for Power BI Service), and enter a valid Redirect URI. This URI is where the authentication token will be sent after successful authentication.

Provide a name for your application
Accounts in this organizational directory only

3. Once the application is registered, note down the Application (client) ID and Directory (tenant) ID. You’ll need these values later in the authentication process.

Once the application is registered
authentication process

4. In the application’s settings, go to Certificates & Secrets. Under Client secrets, click on New client secret.

click on New client secret

Enter a description, select an expiry period, and click Add. 

Enter a description

Note down the generated secret value immediately; it won’t be visible again.

generated secret value immediately

Step 2: OAuth 2.0 Authentication

Now that you have registered your application and obtained the necessary credentials, you can use OAuth 2.0 to authenticate with the Power BI service.

1. Build the URL for user consent. This is where the user will be asked to grant permission to your application.{tenant_id}/oauth2/v2.0/authorize?client_id={client_id}&response_type=code&redirect_uri={redirect_uri}&response_mode=query&scope=

Replace {tenant_id}, {client_id}, and {redirect_uri} with your Azure AD tenant ID, client ID, and the redirect URI you provided during application registration.

2. Direct the user to the constructed authorization URL. After successful authentication, the user will be prompted to grant permission to your application.

3. Once consent is granted, exchange the authorization code for an access token.


Content-Type: application/x-www-form-urlencoded






Replace {tenant_id}, {client_id}, {client_secret}, {authorization_code}, and {redirect_uri} with your values.

4. Include the obtained access token in the headers of your HTTP requests to the Power BI service.

Authorization: Bearer {access_token}

Now, you are authenticated, and you can make requests to the Power BI service, including retrieving information about workspaces and dataflows.

Step 3: Retrieve Workspace and Dataflow Information

Use the Power BI REST API to get information about the workspaces you have access to. This includes identifying the workspace where your target dataflow resides.


Once you have the workspace ID, you can retrieve information about the dataflows in that workspace.


Extract Dataflow Metadata using M language

The Power Query M language is the backbone of Power Query, and it allows you to express data transformations. You can use M expressions to retrieve metadata about a specific dataflow.

Let’s consider a scenario where we want to retrieve information about a specific dataflow, including its name, transformations, and source connections. The following Power Query M code demonstrates how this can be achieved:

    // Specify the dataflow ID
    dataflowId = "your_dataflow_id_here",
    // Create the API URL
    apiUrl = "" & dataflowId,
    // Invoke the Power BI API to get dataflow metadata
    dataflowMetadata = Json.Document(Web.Contents(apiUrl))

This code uses the Power BI REST API to fetch metadata for a specific data flow. Ensure that you replace placeholders like your_dataflow_id_here and your_group_id_here with your actual dataflow and group IDs.

Frequently Asked Questions

Can I retrieve metadata for all dataflows in a workspace?

Yes, you can. By modifying the API URL and utilizing the appropriate endpoints, you can retrieve metadata for all dataflows within a given workspace.

How do I get data from Power Query dataflow?

Click on the “Get Data” option found in the Home tab. Within the “Get Data” dialog box, choose “Power Platform” and then select “Dataflows.” Finally, click on the “Connect” option to proceed.

Is it possible to update dataflow metadata programmatically?

Currently, the Power BI API primarily supports reading metadata. Updates to metadata are typically handled through the Power BI service interface.


By combining the Power BI REST API and the flexibility of the Power Query M language, you can access and leverage metadata information in your applications, scripts, or custom tools. You can further experiment, explore, and integrate these techniques into your Power Query workflows. If you have questions or insights to share, feel free to reach out.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *