Dynamic Data source error in PowerBI Service

[pawankarki23]

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

[pawankarki23]

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]

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.

[pawankarki23]

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. 

[jennratten]

Hi again @pawankarki23 - yes, I saved and both manual and scheduled refresh were successful. 

[pawankarki23]

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. 

[v-saisrao-msft]

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.

[jennratten · 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.  

[v-saisrao-msft]

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.