Forum Discussion

pawankarki23's avatar
pawankarki23
Frequent Visitor
1 year ago
Solved

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 fo...
Need Help
  • jennratten's avatar
    jennratten
    1 year ago

    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.  

pawankarki23's avatar
pawankarki23
Frequent Visitor
1 year ago

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's avatar
jennratten
Icon for Super User rankSuper User
1 year ago

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

 

  • pawankarki23's avatar
    pawankarki23
    Frequent Visitor
    1 year ago

    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. 
    • jennratten's avatar
      jennratten
      Icon for Super User rankSuper User
      1 year ago

      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's avatar
      v-saisrao-msft
      Icon for Community Support rankCommunity Support
      1 year ago

      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.