web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / How to use an environm...
Customer experience | Sales, Customer Insights,...
Suggested answer

How to use an environment variable as the source in a dataverse dataflow

(3) ShareShare
ReportReport
Posted on by Barks365 11

In Dynamics 365 power app, I have a dataverse dataflow that uses power query to connect to a dataverse table. All works fine in dev, however everytime we deploy to another environment we have to manually update the datasource. Is there a way to use an environment variable e.g. in the following line in dataflow advanced editor..

let
  Source = CommonDataService.Database("oursite-dev.crm11.dynamics.com"),
  #"Navigation 1" = . . .

E.g. I want to be able to do something like this..

let
  Source = CommonDataService.Database(EnvironmentVariables("siteUrl")),
  #"Navigation 1" = . . .

Many thanks

I have the same question (0)
  • CU28072136-0 Profile Picture
    CU28072136-0 3 on at
    How to use an environment variable as the source in a dataverse dataflow
    Same feedback here on potential response... @Daivat Vartak (v-9davar): did you actually come up with the response personally? Did you verify it or try it out? Or it was simply an AI tool response which is not verified...?
    We were trying that Option1 and there is apparently no way of specifying a parameter or a variable; you can only use a constant string specified for that CommonDataService.Database()
     
    I would suggest, plese verify yourself any AI proposed solution as sometimes they look feasible but do not actually work!
     
    Probably Option 3 would work to be implemented where a placeholder would be replaced with the environment specific string value.
     
  • PM-27021239-0 Profile Picture
    PM-27021239-0 5 on at
    How to use an environment variable as the source in a dataverse dataflow
    The recommended solution from @Daivat Vartak (v-9davar) won't work, seeing as no flow action of that name exists.  The Power Query action "Refresh a dataflow" does exist, but does not accept parameters as inputs.  When I asked one of the AI's for help on this, it came up with a similar solution, using flow actions that don't exist.
  • Tom_Gioielli Profile Picture
    Tom_Gioielli 2,206 Super User 2025 Season 2 on at
    How to use an environment variable as the source in a dataverse dataflow
    I saw another response come through on this topic and wanted to follow-up with a clarification in case anyone comes across this question.
     
    The connector for Power Automate and Dataflows does not currently allow you to dynamically pass in a parameter value for the dataflow. So, any suggestions of using Power Automate to refresh the flow and pass in a parameter value will not work with currently available functionality.
     
  • Suggested answer
    Daivat Vartak (v-9davar) Profile Picture
    Daivat Vartak (v-9d... 7,833 Super User 2025 Season 2 on at
    How to use an environment variable as the source in a dataverse dataflow
    Hello Barks365,
     

    You've hit upon a very common and frustrating challenge when deploying Dataverse dataflows across environments: the need to manually update data source URLs. Unfortunately, direct use of environment variables within the Power Query M code of a Dataverse dataflow is not directly supported in the way you've described.

    The CommonDataService.Database() function expects a literal string for the server URL, and it doesn't have a built-in mechanism to interpret environment variables.

    However, there are effective workarounds that allow you to achieve the desired dynamic behavior:

    1. Parameterized Dataflows with Power Automate (Recommended)

    • Concept:

      • Create a dataflow with a parameter for the server URL.
      • Use Power Automate to trigger the dataflow and pass the environment-specific URL as the parameter value. 

    • Steps:

      1. Create a Parameter in Dataflow:

        • In your dataflow, go to the Power Query Editor.
        • Create a new parameter (e.g., SiteUrl).
        • Modify your Source step to use the parameter. 
        • Sample Code: let
              Source = CommonDataService.Database(SiteUrl),
              #"Navigation 1" = ...
          in
              #"Navigation 1"

      2. Create an Environment Variable:

        • In your target environment, create an environment variable (e.g., SiteUrl) with the appropriate URL value. 

      3. Create a Power Automate Flow:

        • Use the "When a flow is triggered" trigger.
        • Add a "Get environment variable" action to retrieve the URL from the environment variable.
        • Add a "Run a Dataflow" action.
        • In the "Run a Dataflow" action, pass the retrieved URL as the value for the SiteUrl parameter. 

      4. Trigger the Flow:

        • You can trigger the flow manually or schedule it to run periodically. 
         

    • Advantages:

      • Dynamic URLs without manual editing.
      • Clean and maintainable.
      • Leverages Power Automate's flexibility. 

    • Disadvantages:

      • Requires Power Automate flow setup.
      • Adds a layer of complexity.  

      •  

    2. Configuration Table in Dataverse (Slightly More Complex)

    • Concept:

      • Create a custom configuration table in Dataverse to store environment-specific URLs.
      • Modify your dataflow to retrieve the URL from this table. 

    • Steps:

      1. Create Configuration Table:

        • Create a custom table (e.g., EnvironmentConfiguration) with a text column to store the URL.
        • Add a record for each environment, with the appropriate URL value. 

      2. Modify Dataflow:

        • Add a new query to your dataflow to retrieve the URL from the configuration table.
        • Use the retrieved URL in your Source step.
        • You can use a filter on the configuration table to get the record for the current environment. 
         

    • Advantages:

      • Dynamic URLs within Dataverse.
      • Centralized configuration management. 

    • Disadvantages:

      • Requires creating and managing a custom table.
      • Power Query M code becomes more complex.
      • Could be slower than other options.  

      •  

    3. Deployment Pipeline with Parameter Replacement (Advanced)

    • Concept:

      • Use a deployment pipeline (e.g., Azure DevOps pipelines) to replace the URL in your dataflow's M code during deployment. 

    • Steps:

      1. Parameter Placeholder:

        • In your dataflow's M code, use a placeholder for the URL (e.g., __SITE_URL__). 

      2. Deployment Pipeline:

        • Use a task in your deployment pipeline to replace the placeholder with the environment-specific URL.
        • You can retrieve the URL from an environment variable or a configuration file. 
         

    • Advantages:

      • Fully automated deployment.
      • No manual editing required. 

    • Disadvantages:

      • Requires setting up and managing a deployment pipeline.
      • M code becomes less readable.  

    Recommendation:

    • Parameterized Dataflows with Power Automate (Option 1) is generally the most recommended approach. It's relatively easy to implement, maintainable, and leverages Power Automate's strengths.
    • The configuration table option is also viable, but can add complexity.
    • The deployment pipeline option is best for advanced scenarios with automated deployments.

    Important Notes:

    • Always test your dataflows thoroughly in a non-production environment before deploying to production.
    • Ensure that your environment variables or configuration table records are secured appropriately.

      •  

    By implementing one of these workarounds, you can achieve dynamic data source URLs in your Dataverse dataflows, eliminating the need for manual updates during deployments.

     
    If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.
     
    My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.
     
    Regards,
    Daivat Vartak
  • Suggested answer
    Tom_Gioielli Profile Picture
    Tom_Gioielli 2,206 Super User 2025 Season 2 on at
    How to use an environment variable as the source in a dataverse dataflow
    I'm not aware of a way to get a Dataverse Environment variable into a Dataflow for the purposes of setting your source. Typically, I will use a parameter in the Dataflow itself to quickly and easily update all of my queries with a single update, and it is simply part of the deployment process for myself and my clients.
     
    This might be a great idea to add and upvote as something to add in to the Dataflow feature in a future release.
     
     
     
  • sebastiendb Profile Picture
    sebastiendb 5 on at
    How to use an environment variable as the source in a dataverse dataflow
    I was able to query the environment variables through API while using a user that has environment 'system administrator' role as authentication method. However that API doesn't work with a user having 'system customiser' role. I was unable to call the API using service priniciple (application user which is linked to Entra ID app registration), but it's unclear why I doesn't work, because that service principle (who has 'system administrator' role) does work for authentication of the power automate actions of dataverse.
    Also another problem is that this API to retrieve the environment variables doesn't select the environment the dataflow resides in dynamically, by which I mean I had to manually encode the environment url (DEV/TEST/PROD) in the API call. This is doesn't resolve the problem when deploying a dataflow from one environment (DEV) to another (TEST), because the dataflow should indead dynamically switch connections (based on environment variables of the current environment).
  • Kinson-Lam (WCC) Profile Picture
    Kinson-Lam (WCC) 2 on at
    How to use an environment variable as the source in a dataverse dataflow
    Any clue for this?  I am keen to know how to use environment variables inside dataflow.  thanks
  • fshaikh Profile Picture
    fshaikh 218 on at
    How to use an environment variable as the source in a dataverse dataflow
    Hi, 
     
    Is there any solution to this problem yet?
     
  • UR-06110101-0 Profile Picture
    UR-06110101-0 4 on at
    How to use an environment variable as the source in a dataverse dataflow
    Hello! Did you get a solution on this? I am facing the same issue

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

#1
Rishabh Kanaskar Profile Picture

Rishabh Kanaskar 258

#2
MVP-Daniyal Khaleel Profile Picture

MVP-Daniyal Khaleel 184

#3
Tom_Gioielli Profile Picture

Tom_Gioielli 126 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Hide selecting Contact for potential customer when creating quote

Product updates

Dynamics 365 release plans