Environment Switching in Power BI Custom Connector Using M language(Staging <-> Production)

[Ravindra_]

Hi Team,

I’m developing a Power BI Custom Connector and would like to implement a mechanism to switch between Staging and Production environments dynamically.

Is it possible to design a build where the connector determines the environment (staging or production) at runtime using:

• A system environment variable, or

• A local configuration file like .txt or .json

I’m looking for a reliable approach using M language within the connector logic to support this kind of setup.

Has anyone implemented such functionality in their custom connector or can recommend a best practice?

Appreciate your help and insights!

Thanks,

[jaryszek]

Hi Guys,

new idea for it:
https://community.fabric.microsoft.com/t5/Fabric-Ideas/Skip-Credential-Validation-for-Queries-Exclud...

Please vote!

It means that you can exclude query to be refreshed not only in power bi desktop - also within power bi service without failing because of data source credentials. 

Best,
Jacek

[v-ssriganesh · Accepted solution]

Hi @Ravindra_,
Thank you for reaching out to the Microsoft Fabric Forum Community.

For your scenario implementing environment switching in a Power BI Custom Connector the recommended best practice is to use user-defined parameters via the connector UI. This approach ensures:

• Compatibility with both Power BI Desktop and Power BI Service
• Compliance with security and sandbox restrictions
• Flexibility to scale across different environments without hardcoding or relying on local files

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[burakkaragoz]

Hi Ravindra,

It is possible to implement a mechanism to switch between Staging and Production environments dynamically while developing a Power BI Custom Connector. You can achieve this using M language. Here are two suggestions:

1. Using System Environment Variable:

   • Power BI Custom Connector can read system environment variables. You can use these variables to determine which environment the connector is running in.
   • For example, you can create an environment variable named Environment. This variable can have values like Staging or Production.
   • In M language, you can read this variable and use the appropriate connection strings.

2. Using Local Configuration File:

   • The connector can read a local configuration file (e.g., .txt or .json) where you store environment information.
   • By reading the file content using M language, you can determine which environment the connector is running in.
   • For example, you can have a config.json file with the following structure

{
  "environment": "Staging"
}
​

• In M language, you can read this file and use the appropriate connection strings.

Example M Code:

let
    // Reading environment variable
    environment = Environment.GetVariable("Environment"),
    // Reading configuration file
    config = Json.Document(File.Contents("config.json")),
    environment = config[environment],

    // Connection strings based on environment
    source = if environment = "Staging" then
        "https://staging.api.example.com/data"
    else
        "https://production.api.example.com/data"
in
    source

With these methods, you can dynamically determine which environment your Power BI Custom Connector is running in and use the appropriate connection strings. I hope this helps!

Thanks
translation and formatting supported by AI

[shripathikamath]

I have a question related to the second approach (using a local configuration file.)  Are there special permissions I need to set?  Because I get a security error when trying to read a file placed in the C:\documents\Public folder.

[Deku]

Why not just use parameters to swap connection string or schema like you would with other connectors?