How to Parameterize the ODATA Queries in Power BI

Parameterize the ODATA Queries

To parameterizie ODATA queries in Power BI reports or Fabric dataflows simplifies the replication process across team projects, which is particularly useful when creating an ADO organizational data model. These queries must be executed at the team project level, as there is no method to run them at the ADO organizational level for work items, repositories, testing, or pipelines. By incorporating parameters into the ODATA query, you can easily integrate data from other team projects into the Power BI data model.

This blog post shows how to add the parameter to the Power BI report and the Fabric Dataflows. This allows to quickly reuse the ODATA queries in the same project, or use in other Power BI reports.

Add ADO Organization and Team Project Parameters

This process works in Power BI Desktop and a Fabric Dataflow. These are both Power Query methods to retrieve the data from the ADO analytics.

Select Transform to open the Power Query editor.

Select Manage Parameters -> New Parameter

For this example, I use ADO-Org as the parameter name and set the data type to Text. Enter the name of your ADO organization in the Current Value field.

If you are unsure on what your ADO organization or team name is, follow the steps in this blog.

Select New in the Manage Parameters screen to create another parameter for the Team Project.

Select OK to close the window.

ODATA Queries

Organize the ODATA queries and the parameters in the Power Query Editor

ODATA Queries with Parameters

Using parameters in your ODATA queries makes them easily reusable across different reports or dataflows. Simply copy the ODATA queries to the new report or dataflow and adjust the parameters as needed. This approach minimizes the risk of errors in the ODATA query and ensures that data is pulled from the correct team project.

The ODATA queries must be added to Power BI using the ODATA connector before the parameters can be used. Follow the steps in this blog post on testing the ODATA queries and how to add to the Power BI report.

Work Items:

 Source = OData.Feed("https://analytics.dev.azure.com/" & #"ADO-Org" & "/" & TeamProject & "/_odata/v4.0-preview/workitems?" 
    & "$select=WorkItemId,Title,WorkItemType,State,TagNames,Risk,Severity,ChangedDate,CreatedDate,ClosedDate,ResolvedDate"
 & "&$filter=(CreatedDate ge 2023-01-01Z) and (WorkItemType eq 'Epic')", null, [Implementation="2.0",ODataVersion = 4])
in
    #"Source"

Area Paths:

let
    Source = OData.Feed("https://analytics.dev.azure.com/" & #"ADO-Org" & "/" & TeamProject & "/_odata/v4.0-preview/Areas?"
    & "$select=AreaSK, AreaName, AreaPath, Number,AreaLevel1,AreaLevel2,AreaLevel3,AreaLevel4,AreaLevel5,AreaLevel6,AreaLevel7,AreaLevel8,AreaLevel9,AreaLevel10,AnalyticsUpdatedDate,ProjectSK"
    & "&$expand=Project($select=ProjectName)"      
    , null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
    #"Source"

The parameters can be used in any ODATA query.

The ODATA queries are now portable to another Power BI report. Just copy and paste the M code from the Power Query editor and add the parameters to the new report. Change the ADO org or the Team Project parameters and refresh the data.

Hope this helps with your experience reporting with data from ADO!