Dynamic Data source error in PowerBI Service

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. 

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.

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.