Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi Everyone, I am trying to fetch data from ADO Board using Power query using Odata.Feed. I have multiple ADO Boards acrros the organization and would like to make the query as dynamic as possible for reuse....Currently I am using Following
let
// Define Parameters
Org = orgName,
Project = projectName,
TeamName = teamName,
TodayDate = DateTimeZone.FixedUtcNow(),
StartDate = DateTimeZone.ToText(Date.AddDays(DateTimeZone.FixedUtcNow(), -30), "yyyy-MM-dd") & "T00:00:00Z",
// Determine the appropriate board name based on project template
BoardName =
if projectType = "Scrum" then "Backlog items"
else "Stories",
// Encode URL parts
EncodedProject = Uri.EscapeDataString(Project),
EncodedTeamName = Uri.EscapeDataString(TeamName),
EncodedBoardName = Uri.EscapeDataString(BoardName),
// Build OData URL
BaseUrl = "https://analytics.dev.azure.com/" & Org & "/" & EncodedProject & "/_odata/v3.0-preview/WorkItemBoardSnapshot?",
ApplyFilter =
"$apply=filter(" &
"Team/TeamName eq '" & TeamName & "' " &
"and BoardName eq '" & BoardName & "' " &
"and DateValue ge " & StartDate &
")/groupby(" &
"(DateValue,ColumnName,LaneName,State,WorkItemType,AssignedTo/UserName,Area/AreaPath), " &
"aggregate($count as Count)" &
")",
FullUrl = BaseUrl & ApplyFilter,
// Fetch Data
Source = OData.Feed(
FullUrl,
null,
[Implementation = "2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]
),
#"Filtered Rows" = Table.SelectRows(Source, each true)
in
#"Filtered Rows"
Here the parameters are defined as power query parameters,
PowerBI Desktop works fine for this query but when I try to schedule a refresh, it gives me Dynamic DataSource error. How can i resolve this issue such that I can reuse the same query for different org, project etc as much as possible. Please guide me. Thank you..
Solved! Go to Solution.
Hi @pawankarki23 - I have confirmed that if I use the same code I posted above in a dataflow gen1 I also get the dynamic data source error. This was successful for me as a dataflow gen2 CICD.
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
Found that we can use dynamic url in odata connection using dataflow gen 2 cicd only. I could schedule refresh and found the dataflow refresh working as well. This is a great way to use dynamic odata URL to pull only data as needed and also avoid "dynamic data source" issue generated at the PowerBI service.
Hello @pawankarki23 - I was able to get this to work by using a dataflow Gen2 and making a few modifications to the code. I also changed the date parameter to a data offset in number of days and provided some additional examples of how you can further parmeterize this, should you wish to do so.
let
// Define Parameters (these should be defined in Power BI as query parameters)
_Org = orgName,
_Project = projectName,
_TeamName = teamName,
_ProjectType = projectType,
_DateOffset = startDateOffsetInDays,
// Calculate the start date in DateTimeZone format.
_StartDate = DateTimeZone.ToText(Date.AddDays(DateTimeZone.FixedUtcNow(), _DateOffset), "yyyy-MM-ddTHH:mm:ssZ"),
// Determine the appropriate board name based on project template
//BoardName = if _ProjectType = "Scrum" then "Backlog items" else "Stories",
_BoardName = if _ProjectType = "Scrum" then "Stories" else "Backlog Items", // I had to use this one to get results for my project
// Encode URL parts
_EncodedProject = Uri.EscapeDataString(_Project),
_EncodedTeamName = Uri.EscapeDataString(_TeamName),
_EncodedBoardName = Uri.EscapeDataString(_BoardName),
// Build OData URL
Url = "https://analytics.dev.azure.com/" & _Org & "/" & _EncodedProject & "/_odata/v4.0-preview/WorkItemBoardSnapshot?"
&"$apply=filter( "
&"Team/TeamName eq '" & _TeamName & "' "
&"and StartDate lt now() "
& "and BoardName eq '" & _BoardName & "' "
&"and DateValue gt " & _StartDate & " "
// Additional examples that could be included
// &"and IsCurrent eq true "
// &"and startswith(Area/AreaPath,'" & varAreaPathPrefix & "') "
// &"and StateCategory ne '" & varStateCategory_Excl & "' "
// &"and DateValue le now() "
// &"and DateValue ge Iteration/StartDate "
// &"and DateValue le Iteration/EndDate "
// &"and Iteration/StartDate le now() "
// &"and Iteration/EndDate ge now() "
// &"and WorkItemType eq 'Task' "
&") "
&"/groupby( "
&"(Team/TeamName,Area/AreaPath,DateValue,ColumnName,LaneName,State,AssignedTo/UserName,WorkItemType), "
&"aggregate($count as Count) "
&") &$orderby=DateValue,State", // additional example
// &"&$top=100 ", // additional example
// Result
data = OData.Feed(Url, null, [Implementation = "2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]),
RemoveComplexColumns = Table.RemoveColumns(data, Table.ColumnsOfType(data, {type table, type record, type list, type nullable binary, type binary, type function}))
in
RemoveComplexColumns
You can also trigger this to run using a data pipeline and let the pipeline hold your list of teams/parameter values and the dataflow can interate through those. Please let me know if you'd like me to help with that.
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
Hi @jennratten , Thank you for testing out. I am parametarizing all those variables as power query parameters. Did you try to save the dataflow and set a scheduled refresh in dataflow gen 2 with the query that you have wrriten? I only have access to dataflow gen 1 yet. Please let me know if you could do that so that I can ask for upgrade of our powerbi env to MS Fabric. Thank you again.
Hi again @pawankarki23 - yes, I saved and both manual and scheduled refresh were successful.
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
Hi @jennratten , Thank you so much for trying it out. I am using exact following query
Hi @pawankarki23 ,
I wanted to check if you had the opportunity to review the information provided by @jennratten . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Hi @pawankarki23 - I have confirmed that if I use the same code I posted above in a dataflow gen1 I also get the dynamic data source error. This was successful for me as a dataflow gen2 CICD.
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
Hi @pawankarki23,
Thank you for reaching out to the Microsoft Fabric Forum Community.
The "Dynamic Data Source" error you're encountering is a known limitation in the Power BI Service, as documented by Microsoft. To overcome this, you should use a static base URL in your query.
Please refer the below Document:
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |