How to Manage Development with Pipelines, Dataflows and Parameters

Owned by Ann Crawford (Unlicensed), created
Last updated: Jul 09, 2021
2 min read

Life cycle management, insures that changes in development will not break production code. Connecting datasets to dataflows and adding parameters to the dataset allows pipelines to move PBI content through the development cycle and isolate changes.

This document describes how to update the dataset m-query to accommodate workspace and dataflow parameters. The code assumes that the dataset using a dataflow and that dataflow and entity have the same name throughout the deployment pipeline.

Initial dataset development connects directly to the dataflow using the Power BI Query editor. The editor inserts the GUIDs for the dataflow and workspace as shown in the info block below. This example shows getting an entity from a dataflow with additional

Instructions

  1. Edit the dataset in Power BI Desktop > edit query

  2. Create the Parameter to hold the workspace value.

    1. Manage Parameter Menu > New Parameter

      1. Parameter Name ,Description, Type (see screen shot 1 below). The type cannot be any if this is to be managed dataset rules in the deployment pipeline.

  3. Edit the table queries in the dataset replacing the GUID code with the parameter code. (see info block below) In this example, the parameter is called WorkspaceFilter. The m-query shown in the example has transformation code after selecting the dataflow entity. This code (shown in green) remains unchanged. The italicized code show the replacement code.

 

The query as initially development using GUIDS

let
Source = PowerBI.Dataflows(null),
#"17b475e9-ae6c-4c12-a786-c60bf1b21dc1" = Source{[workspaceId="17b475e9-ae6c-4c12-a786-c60bf1b21dc1"]}[Data],
#"418157f9-4e89-4739-9761-df5ad04f8701" = #"17b475e9-ae6c-4c12-a786-c60bf1b21dc1"{[dataflowId="418157f9-4e89-4739-9761-df5ad04f8701"]}[Data],
Fund1 = #"418157f9-4e89-4739-9761-df5ad04f8701"{[entity="Fund"]}[Data],
#"Renamed Columns" = Table.RenameColumns(Fund1,{{"Core_Fund", "Core Fund"}})
in
#"Renamed Columns"

The same query with a workspace Parameter

let
#"Data Flow" = "Data Warehouse",
#"Entity Name" = "Fund",
Source = PowerBI.Dataflows(null),

#"Filtered Rows" = Table.SelectRows(Source, each ([workspaceName] = WorkspaceFilter)), #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"dataflowName", "Data"}, {"dataflowName", "Data.1"}),

#"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each ([dataflowName] = #"Data Flow")), #"Data 1" = #"Filtered Rows1"{0}[Data.1],

#"Filtered Rows2" = Table.SelectRows(#"Data 1", each ([entity] = #"Entity Name")),

Fund1 = #"Filtered Rows2"{[entity= #"Entity Name"]}[Data]
#"Renamed Columns" = Table.RenameColumns(Fund1,{{"Core_Fund", "Core Fund"}, {"Student Fee Type", "Student Fees Type"}})
in
#"Renamed Columns"

 

Breaking Down the m query

variables are defined with a “#”

let
#"Data Flow" = "Data Warehouse",
#"Entity Name" = "Fund",
Source = PowerBI.Dataflows(null), < = gives a list of all dataflows in all workspaces
#"Filtered Rows" = Table.SelectRows(Source, each ([workspaceName] = WorkspaceFilter)), <= filter list by workspace name
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"dataflowName", "Data"}, {"dataflowName", "Data.1"}), <== show data and dataflow name from the list
#"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each ([dataflowName] = #"Data Flow")), <== filter on the dataflow name
#"Data 1" = #"Filtered Rows1"{0}[Data.1], <== expand the data from the filtered list

#"Filtered Rows2" = Table.SelectRows(#"Data 1", each ([entity] = #"Entity Name")), <== select the entity from the dataflow

Fund1 = #"Filtered Rows2"{[entity= #"Entity Name"]}[Data] <== expand the data from the entity

 

Screen Shot 1 - Manage Parameter

 

 

 

Related articles