In this blog post, I'll share few tricks that help me simplifying the deployment of Fabric Dataflows Gen2 items across multiple workspaces and customers. When you create multiple dataflows that depend on other Fabric items as the source or destination, you end up with M expressions that navigate to the actual Fabric item IDs. To reuse these dataflows, you can end up needing to manage a very long list of items and their IDs by workspace.
As a background: The need to reuse the M code or dataflows definition across workspaces is an essential challenge in BI DevOps / CICD use cases. For example, you can use Fabric Deployment Pipelines to manage different stages like Development, Test and Production, each with their own workspace and unique instance. They all must share the same M expressions at the dataflows level. In such use cases, you can use Power Query Parameters or Fabric Variables to define the Item IDs in each workspace. But you must maintain the list all items by their IDs across the different workspaces.
In this post, I will share 3 tips that helped me reduce the complexity of my dataflow deployments by minimizing the need to manage item IDs to zero.
Let's review few examples first and start with the base line methods that are known and documented. Then, we will move to the tips.
The Lakehouse as a Data Destination
When your data destination in a dataflow is a Lakehouse, the mashup.pq file of the dataflow will have this code:
shared TestTable_DataDestination = let
Pattern = Lakehouse.Contents([HierarchicalNavigation = null, CreateNavigationProperties = false, EnableFolding = false]),
Navigation_1 = Pattern{[workspaceId = "1ab62668-524a-44de-bee6-13888bc8ed1a"]}[Data],
Navigation_2 = Navigation_1{[lakehouseId = "b223e809b-4499-4fe5-a403-8e46dbcf2e09"]}[Data],
TableNavigation = Navigation_2{[Id = "TestTable", ItemKind = "Table"]}?[Data]?
in
TableNavigation;In the example above, you need to create two parameters or Fabric variables. The first for the workspace ID and the second the Lakehouse ID (Assuming you keep unique Lakehouse per deployment stage).
Here is how you can define the workspace ID in your M code, after you created it as a variable in a Fabric variable library (learn more here and here):
Variable.ValueOrDefault("$(/**/Variables/paramWorkspaceID)", "1ab62668-524a-44de-bee6-13888bc8ed1a")And here is the variable of the Lakehouse ID
Variable.ValueOrDefault("$(/**/Variables/paramLakehouseID)", "b223e809b-4499-4fe5-a403-8e46dbcf2e09")You can also create public parameters in your dataflows (Learn more here). The destination logic in M will be the same. In our example, if you name the variables or parameters as paramWorkspaceID and paramLakehouseID, you can modify the M code as follows:
shared TestTable_DataDestination = let
Pattern = Lakehouse.Contents([HierarchicalNavigation = null, CreateNavigationProperties = false, EnableFolding = false]),
Navigation_1 = Pattern{[workspaceId = paramWorkspaceID]}[Data],
Navigation_2 = Navigation_1{[lakehouseId = paramLakehouseID]}[Data],
TableNavigation = Navigation_2{[Id = "TestTable", ItemKind = "Table"]}?[Data]?
in
TableNavigation;Note, that the editing is not available using the UI. You will need to your your code editor (e.g. Visual Studio Code that is connected to the Git repository of your development workspace).
The Lakehouse as a Source
When you import data from a Lakehouse, a Warehouse, a Dataflow or other connectors that uses the navigation steps to identify individual Fabric items, the M code will include the workspace ID and the item ID. For example, here is the M code where I import data from a specific Lakehouse that is located a specified workspace. The code refers to both workspace and Lakehouse IDs.
let
Source = Lakehouse.Contents(null),
Navigation = Source{[workspaceId = "1ab62668-524a-44de-bee6-13888bc8ed1a"]}[Data],
#"Navigation 1" = Navigation{[lakehouseId = "b223e809b-4499-4fe5-a403-8e46dbcf2e09"]}[Data],
#"Navigation 2" = #"Navigation 1"{[Id = "TestTable", ItemKind = "Table"]}[Data]
in
#"Navigation 2"The trivial fix you may already use is to replace the IDs with variables or parameters paramWorkspaceID and paramLakehouseID:
let
Source = Lakehouse.Contents(null),
Navigation = Source{[workspaceId = paramWorkspaceID]}[Data],
#"Navigation 1" = Navigation{[lakehouseId = paramLakehouseID]}[Data],
#"Navigation 2" = #"Navigation 1"{[Id = "TestTable", ItemKind = "Table"]}[Data]
in
#"Navigation 2"The Upstream Dataflows as a Source
When you import data from an upstream dataflow (Gen1 or Gen2), the auto-generated M code refers to the dataflow ID in the navigation steps. Here is an example:
let
Source = PowerPlatform.Dataflows([]),
#"Navigation 1" = Source{[Id = "Workspaces"]}[Data],
#"Navigation 2" = #"Navigation 1"{[workspaceId = "1ab62668-524a-44de-bee6-13888bc8ed1a"]}[Data],
#"Navigation 3" = #"Navigation 2"{[dataflowId = "ba55ad48-1dc5-413a-8d8d-fdf0986af382"]}[Data],
#"Navigation 4"= #"Navigation 3"{[entity="TestTable"]}[Data]
in
#"Navigation 3"To reuse the dataflow, you would create a new variable or a parameter for each upstream Dataflow ID. For example, in this code we use paramDataflowId in the M code:
let
Source = PowerPlatform.Dataflows([]),
#"Navigation 1" = Source{[Id = "Workspaces"]}[Data],
#"Navigation 2" = #"Navigation 1"{[workspaceId = paramWorkspaceID]}[Data],
#"Navigation 3" = #"Navigation 2"{[dataflowId = paramDataflowID]}[Data],
#"Navigation 4"= #"Navigation 3"{[entity="TestTable"]}[Data]
in
#"Navigation 4"The Challanges
Working with many interconnected dataflows can become difficult to manage. Imagine you have 10 dataflows that are used as a source in other dataflows, you will need to manage a list of 10 IDs per deployment stage. So, if you use Development and Production workspaces, you will need to keep a table with 20 IDs and set these IDs in their proper locations (e.g. in relevant Fabric Pipelines, Variables, Notebooks).
| Dataflow Name | Dataflow ID in Development Workspace | Dataflow ID in Test Workspace |
| Dataflow 1 | Guid ID 1a | Guid ID 1b |
| Dataflow 2 | Guid ID 2a | Guid ID 2b |
| Dataflow 3 | Guid ID 3a | Guid ID 3b |
| ... | ... | ... |
| Dataflow 10 | Guid ID 10a | Guid ID 10b |
As you work with more workspaces, the list of connections to manage grows linearly.
You will need to manage the same for other item types. For example, imagine you have one Lakehouse and two Warehouses as sources or destinations. The list expands by 8 connection IDs.
| Item | Item ID in Development Workspace | Item ID in Test Workspace |
| Workspace | Guid ID 11a | Guid ID 11a |
| Lakehouse | Guid ID 12a | Guid ID 12b |
| Warehouse 1 | Guid ID 13a | Guid ID 13b |
| Warehouse 2 | Guid ID 14a | Guid ID 14b |
By now, you can understand the gist of it. Managing GUIDs is not the best way to use our time, and small mistakes of using the wrong IDs can be risky causing errors or even worse. Such mistakes can go unnoticed and lead to data quality issues and inaccurate reports.
Tips and Tricks to Solve the Challenge
There are several methods you can use to reduce the number of IDs to manage. You can combine between the methods to simplify or even reduce the list of your Fabric item IDs to zero.
Tip #1: Reduce the need to Stage data In Dataflows Gen2
If you use Dataflows Gen2, avoid the use of Staging of upstream dataflows as the source of your downstream dataflows. Instead, you can store the data in one of the supported destinations like a Lakehouse or a Warehouse. This will reduce the navigation steps from 3 to 2 and avoid the need to refer to the upstream dataflow.
let
Source = Lakehouse.Contents(null),
Navigation = Source{[workspaceId = paramWorkspaceID]}[Data],
#"Navigation 1" = Navigation{[lakehouseId = paramLakehouseID]}[Data],
#"Navigation 2" = #"Navigation 1"{[Id = "TestTable", ItemKind = "Table"]}[Data]
in
#"Navigation 2"Tip #2: Navigate to Fabric Items using their names instead of IDs
When Fabric Items are used as the source in Dataflow, you can create parameters / variables with their names instead of their IDs. In the relevant navigation step, use lakehouseName as the field to filter by instead of lakehouseId. You can also define the item name as a parameter/variable (Even though you will not need to ever change its value).
Here is the navigation step before we apply this tip:
#"Navigation 1" = Navigation{[lakehouseId = paramLakehouseId]}[Data],And here is the step after the change:
#"Navigation 1" = Navigation{[lakehouseName = paramLakehouseName]}[Data],How will this change impact the number of item IDs to manage. This change will not only reduce the number of IDs to manage by one. The rationale here is that you can use the same item name across all staging workspaces. This way, you will not need to maintain item IDs per workspace. Instead, you can enter the item name only once. The name is not likely to ever change. This tip becomes even more effective for consultants or software vendors like us that distribute the same dataflows across many customers. You can define the item name once in the entire lifecycle of the dataflow and enter it as a parameter. In this example, I import data from TestTable in a lakehouse.
let
Source = Lakehouse.Contents(null),
Navigation = Source{[workspaceId = paramWorkspaceID]}[Data],
#"Navigation 1" = Navigation{[lakehouseName = paramLakehouseName]}[Data],
#"Navigation 2" = #"Navigation 1"{[Id = "TestTable", ItemKind = "Table"]}[Data]
in
#"Navigation 2"The same logic is applied to other items. For example, if you use upstream dataflows as the source, you can navigate to the dataflow using the dataflowName attribute instead of dataflowId.
Instead of the original navigation step:
#"Navigation 3" = #"Navigation 2"{[dataflowId = "ba55ad48-1dc5-413a-8d8d-fdf0986af382"]}[Data],
you can navigate to the dataflow by referring to its actual name:
#"Navigation 3" = #"Navigation 2"{[dataflowName = "Upstream sales dataflow"]}[Data],In many scenarios, you use logical names for the dataflows that already represent their business logic. So, the added-value of creating a parameter/variable for the dataflow name is not high.
Using this approach, you can reduce the number of the Fabric item IDs to Zero. There is one last tip to share...
Tip #3: Navigate by Workspace name instead of Workspace ID
The final optimization is related to how you reference the current workspace. In any navigating to Fabric items in Dataflows - for both source and destinations, you will find a reference to the workspace ID where the item exist. To simplify the reuse of your dataflows, you can use a single parameter and store the workspace name. Then use workspaceName as the attribute instead of workspaceId.
For example, when you navigate to any Fabric item, in the UI you are asked to select the workspace where the item is located. Power Query will generate such line in the M code:
Navigation = Source{[workspaceId = "1ab62668-524a-44de-bee6-13888bc8ed1a"]}[Data],
You can replace it with this code after you create a parameter or a variable for the workspace name.
In the example below, I created paramWorkspaceName as the variable. It can be defined once in the Fabric Variables Library.
Variable.ValueOrDefault("$(/**/Variables/paramWorkspaceID)", "1ab62668-524a-44de-bee6-13888bc8ed1a")Next, in the dataflow M code, I used workspaceName as the attribute to navigate to the workspace name.
Navigation = Source{[workspaceName = paramWorkspaceName]}[Data],Following the tips above, you can reduce the dependencies on IDs to zero, and have a clearer list of parameters and values to manage.
Final words
The tips above were implemented in our product, BI Pixie. Customers of BI Pixie that prefer to deploy our solution in their Fabric account can import our source code with its 20 dataflows using Git-integrated workspace or fabric-cicd. To avoid the need to manage item IDs, we applied the tips in this post, and that led us to zero IDs to manage. We provide a single customer-facing variable for the workspace name. BI Pixie customers can deploy the entire solution in their tenant using the Git integration or fabric-cicd, without the need to manage any ID.
This screenshot shows a screenshot of BI Pixie pipeline in Fabric to illustrate the dependencies between the 20 dataflows.
I hope you found this post useful.
