Last week, I was challenged by moving my Power BI dataset through the next stage in my DTAP pipeline. I simply wanted to re-assign my connections to the data source in the next stage. In my case, I created a separate workspace including separate dataflows for each stage. Since de dataflows connector in Power BI is calling some Ids hard coded in the Power Query code, I was forced to manually change these Ids. In my opinion there should be an easier way to do this…

The PowerBI.Dataflows(null) connector?

The dataflows connector in Power BI desktop is a default connector. Once we connect, you will get an overview of all your workspaces which does include dataflows and all underlying entities.

Let’s assume that I do have three Power BI workspaces and each one contains a dataflow. All entities do have the same name and same underlying table name. Since we already recognize the environment by the workspace name, I always use the same entity and tables names across all DTAP stages.

As an example, I just selected the table PimpYourDataflowConnector in the development workspace and loaded this one. When we take a look at the generated code by Power Query in the advanced editor, it will look something like this:

let
    Source = PowerBI.Dataflows(null),
    #"12a34567-1ab2-1234-a123-1a2b34cde567" = Source{[workspaceId="12a34567-1ab2-1234-a123-1a2b34cde567"]}[Data],
    #"9z8yx765-zyx9-98z7-9zy8-zyxw987vu654" = #"12a34567-1ab2-1234-a123-1a2b34cde567"{[dataflowId="9z8yx765-zyx9-98z7-9zy8-zyxw987vu654"]}[Data],
    PimpYourDataflowsConnector = #"9z8yx765-zyx9-98z7-9zy8-zyxw987vu654"{[entity="PimpYourDataflowsConnector"]}[Data]
in
    PimpYourDataflowsConnector

Looking at this generated code, it is containing my workspaceId and dataflowId hard coded. Actually, the first line in this code where the function PowerBI.Dataflows(null) is called, is probably the most interesting one. This step returns all dataflows where the logged-in user has access to on tenant level.

Make it flexible!

Power Query is an expression-based language. Every step has its own result, which is called as an input for the step afterwards. Looking at the code, we also see some filters applied, for example in the source step, where we filter the column workspaceId on the value called afterwards. Besides that, we see [Data] called after the filter. With this, Power Query will only return the result in the column called Data. 

Now, we know this, we can build our own logic which makes it easier to switch between the dataflows in different workspaces. We will do this by following the below steps:

1. As first step we call the connector PowerBI.Dataflows(null). As already mentioned, this will result in a list of all our dataflows across the tenant where the logged-in user has access to.
   
2. Second, we create a parameter for our workspace name which we will use for filtering in the next step. The parameter should be type text to make the filter work. Of course, it can be handy to add all possible results as a list in the parameter. But know that you won’t be able to see this list when you want to change the parameter in the Power BI Service.
   
   Read more about changing parameters in the Power BI service in my previous blog post about Parameterize your datasource.
3. Next is to apply the created parameter as a filter to our table which includes all dataflows. We can do this by simply adding a filter with the dropdown on the column workspaceName and apply the following:
   
4. Now, we only have one workspace left. We want to have the tables included in the data column. We can get this data by drilling down on the Data column. But if we do so, Power Query will still apply a filter based on the workspaceId. To avoid that, we will right-click on the Data column and do remove other columns.
5. Only the Data column is left so far. As a next step, we will expand the Data column to get all underlying content and expand all columns. As a result, we will see all available dataflows for the specified workspace. In this example that is only one dataflow, but this could result in multiple dataflows, so multiple rows.
6. We can do a simple drill down now, by clicking on Table in the column which is called Data.1. If we do, Power Query will create a step with the following code:
   #”Expanded Data”{0}[Data.1]
   The part where you see {0} means that it returns the result for the first row. (Power Query starts counting at zero). For now, this works. But maybe we add new dataflows in the future which might impact the order of rows. As a result of that, the first row might be different, and return a different dataset.To avoid issues like this, please first add a filter where you only select the required dataflow. You can do this again by using a parameter. I just hardcoded filtered the dataflow name here. Simply because my dataflow does have the same name in each stage (Dev / Acc / Prd).
7. After filtering, we only have one dataflow left. Now, we can easily click the Table element in the Data.1 column to be expanded.
   
   This will result in a list of all underlying entities.
8. For entities, the same story applies as explained for dataflows in step 6. It is better to first apply a filter based on the entity you want to extract, before clicking the Table object in the Data column.In my example, I applied a filter on the entity column to equal the entity name “PimpYourDataflowsConnector”. This step will result in only one row left. This one row includes the entity you want to extract.
9. The last step is to expand the Table object in the Data column. By clicking the Table object, Power Query will again apply a hard-coded filter in the code which we don’t want to. In this case that doesn’t matter so much, because this filter will be the same as we applied in step 8.If we want to optimize this, we can do that with first apply an additional step to remove all columns except the Data column. If you expand the table object afterwards, this will result in the table included in the entity.

Wrap things up

In the previous nine steps, we have optimized our dataflows connector to easily switch over workspaces. This will come in handy when you want to switch your data source by moving to the next stage in your development process.

Looking at the code we generated, you will still have a few things hard coded. That are the dataflowName and entityName. Besides these objects, there are hard-coded column names for expanding in the code as well. In the Expanded data step, that doesn’t make a big difference, since this is all meta data of the dataflows. But our last step, Expanded Data1 contains all column names included in our entity. In case we add another column to our entity in the future, it won’t show up. To make this more flexible, please read my blog about Dynamically expanding and appending tables with Power Query.

To get a final overview, below is all Power Query code which we generated.

let
    Source = PowerBI.Dataflows(null),
    #"Filtered Rows" = Table.SelectRows(Source, each [workspaceName] = WorkspaceFilter),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"dataflowId", "dataflowName", "dataflowDescription", "Data", "ItemKind", "ItemName", "IsLeaf"}, {"dataflowId", "dataflowName", "dataflowDescription", "Data.1", "ItemKind", "ItemName", "IsLeaf"}),
    #"Filtered Dataflow" = Table.SelectRows(#"Expanded Data", each [dataflowName] = "DemoEntity"),
    #"Expand Data.1 Column" = #"Filtered Dataflow"{0}[Data.1],
    #"Filtered Entity" = Table.SelectRows(#"Expand Data.1 Column", each [entity] = "PimpYourDataflowsConnector"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Entity",{"Data"}),
    #"Expanded Data1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Environment"}, {"Environment"})
in
    #"Expanded Data1"

Template

To make it even easier for you, I generated a template file. Please feel free to download this template file and reuse it. Since I don’t know which columnsnames your dataflow consists of, I’ve added some more logic to the Power Query to dynamically expand the table. As already mentioned in the section before, you can read how to do this in my previous blog post.

By opening the template file, you will be prompted to enter three parameters. These three parameters will be your workspaceName, dataflowName and entityName. After providing this information, it will load your table.

You can download the template file here.