Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
pawankarki23
Frequent Visitor

Dynamic Data source error in PowerBI Service

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..

1 ACCEPTED 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

View solution in original post

8 REPLIES 8
pawankarki23
Frequent Visitor

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. 

jennratten
Super User
Super User

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



jennratten_1-1747143345645.png

jennratten_2-1747143428217.png

 

 

jennratten_0-1747143083633.png

 

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 

let

    // --- Dynamic start date (30 days ago) in OData-compliant format ---
    startDate = DateTimeZone.ToText(Date.AddDays(DateTimeZone.FixedUtcNow(), offset), "yyyy-MM-ddTHH:mm:ssZ"),

    // --- Construct dynamic URL with embedded startDate ---
    url =
        "https://analytics.dev.azure.com/" & org & "/" & project & "/_odata/v3.0-preview/WorkItemBoardSnapshot?" &
        "$apply=filter(" &
            "Team/TeamName eq '" & team & "' " &
            "and BoardName eq '" & boardName & "' " &
            "and DateValue ge " & startDate & " " &
        ")/groupby(" &
            "(DateValue,ColumnName,LaneName,State,WorkItemType,AssignedTo/UserName,Area/AreaPath), " &
            "aggregate($count as Count)" &
        ")",

    // --- Load data using dynamic URL (only works in Desktop) ---
    Source = OData.Feed(
        url,
        null,
        [Implementation = "2.0", ODataVersion = 4, OmitValues = ODataOmitValues.Nulls]
    )

in
    Source
 
All the parameters are defined as power query parameters. I could create a dataflow gen 2 using the query in my Fabric. But, when i published the dataflow, it complains about the dynamic data source.  Am I missing something here?? -- Please see screenshot atttached. 1.png

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

v-saisrao-msft
Community Support
Community Support

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. 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.