A Dynamic Method for Referencing Workspaces and Dataflows - Adolfo J. Socorro: Data Analytics and AI

Imagine publishing a Power BI report—only to find out it’s still pulling data from QA. Frustrating, right? In this article, we’ll explore a structured approach to eliminate such errors and streamline workspace and dataflow references automatically.

The Challenge

Consider an architecture where you ingest and transform data into a dataflow layer and then you build semantic models by making references to dataflows. Additionally, assume that you have separate workspaces for production and QA.

The semantic models in this architecture will by default have hard-coded GUIDs to reference workspaces and dataflows. That is what happens, for example, if we connect to an entity called Customer in a dataflow via Get Data in Power BI Desktop. The resulting, automatically generated M code looks like this:

let
    Source = PowerPlatform.Dataflows(null),
    Workspaces = Source{[Id=”Workspaces”]}[Data],
    #”0e76ab6d-e5db-4339-a723-f35ebcbb4730″ = Workspaces{[workspaceId=”0ee5db6d-e5db-4339-a723-f35ebcbb4730″]}[Data],
    #”34f42f44-8dca-460c-9470-370c6498e685″ = #”0ee5db6d -e5db-4339-a723-f35ebcbb4730″{[dataflowId=”34460c44-8dca-460c-9470-370c6498e685″]}[Data],
    Customer_ = #”34460c44-8dca-460c-9470-370c6498e685″{[entity=”Customer”,version=””]}[Data]
in
    Customer_

The code connects to a table (called “entity” in the code) named Customer in a workspace and dataflow that are referenced by their corresponding GUIDs.

Once a report is ready to go live, QA GUIDs will have to be replaced with production GUIDs. Not only is this tedious and time-consuming (since you have to manually retrieve GUIDs), but it’s also easy to miss a reference—especially when working with multiple dataflows. You can end up with a report that uses 90% production data and 10% QA data—and difficult to notice the mismatch.

Now, let’s walk through a full scenario and our solution step by step.

The Architecture

Consider an architecture with production artifacts like this:

In this architecture:

The Customer Master and Product Master workspaces each contain a single dataflow.
The Shipments workspace contains two dataflows.
Each workspace and dataflow has a corresponding QA version:

The Solution

Our solution involves using an Excel file to store the GUIDs, instead of hardcoding them. Our semantic models will dynamically reference this file, making it easy to switch between QA and production environments—something we will achieve by changing the value of one simple parameter.

First, in our Power BI report we reference the Excel file with the GUIDs. The file looks like this:

Creating this file might seem like a lot of work, but besides the benefits of the almost effortless and automatic switching between QA and prod, it can be reused across semantic models.

Next, we create a parameter that controls whether the semantic model will pick up production or QA GUIDs:

The Environments File Path parameter gives the address to the Excel file with all the GUIDs. A query called Environments connects to the file and load its data.

Finally, the dataflow references make use of those GUIDs via lookup into the Environments query. To achieve that, we create queries to get the list of tables (or entities) present in each dataflow we are interested in. We don’t connect to the dataflows via Get Data, as we did before: that would get us the ugly code above and force us to intervene GUIDs directly.

With that in mind, let’s further work our example. We organize our queries like this:

One query for each dataflow we want to connect to. For instance, Customer Master Dataflow. Its result will be a list of the tables in the dataflow.
One query for each dataflow table we want to load. Each of these queries will reference the dataflow queries. For example, a query Customer that references the Customer table given by the Customer Master Dataflow query.

The code for Customer Master Dataflow follows. We will reuse it for the other dataflows by modifying the names indicated by the “// change this name” comments:

let
    Source = PowerPlatform.Dataflows(null),
    Workspaces = Source{[Id=”Workspaces”]}[Data],
    WorkspaceID = Table.SelectRows(Environments,
        // change this name
        each [Key] = “Customer Master Workspace ID”
            and [Environment] = Environment){0}[Value],
    DataflowID = Table.SelectRows(Environments,
        // change this name
        each [Key] = “Customer Master Dataflow ID”
            and [Environment] = Environment){0}[Value],
    Workspace = Workspaces{[workspaceId=WorkspaceID]}[Data],
    Dataflow = Workspace{[dataflowId=DataflowID]}[Data],
    #”Changed Type” = Table.TransformColumnTypes(Dataflow,{{“entity”, type text}, {“entityName”, type text}})
in
    #”Changed Type”

The code looks up the GUIDs in the Environments query based on the names in the “Key =” clauses. Note too the “[Environment] = Environment,” clauses. They select whether we are picking up production or QA GUIDs based on the Environment parameter.

When our Environment parameter’s value is QA, the above code will pick up these GUIDs:

This approach ensures that instead of hardcoding workspace and dataflow IDs, we dynamically reference them based on the selected environment (QA or Production). This eliminates the need for manual intervention and reduces errors.

As we further expand our sample development, we organize all dataflow queries in a folder:

To get the customer master dataflow table, we reference the Customer Master Dataflow query:

and then filter for “Customer”, its name in the dataflow:

resulting in this code:

let
    Source = #”Customer Master Dataflow”,
    #”Filtered for Customer Entity” = Table.SelectRows(Source, each ([entity] = “Customer”)),
    Data = #”Filtered for Customer Entity”{0}[Data]
in
    Data

Publishing: Moving from QA to Production

Once the report is tested and ready to be published to a production workspace, we only need to change the value of the Environment parameter to “Prod” and voilà, all GUIDs will be the production ones:

Bonus Tip

We suggest you add to your Power BI report template (you have one, correct?) dataflow queries for the basic data you probably reuse constantly, such as customer and product masters.

Summary

To implement this solution, follow these steps:

Conclusion

By structuring Power BI reports with this method, we ensure seamless transitions from QA to production, reduce manual errors, and enhance maintainability. Make it a part of your report template, and you’ll never have to worry about mismatched environments again.

Have you faced similar challenges with workspace and dataflow references? Share your experiences or questions in the comments!