Forum Discussion
Dynamic Data source error in PowerBI Service
- 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.
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.
- pawankarki231 year agoFrequent Visitor
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.
- jennratten1 year ago
Super User
Hi again pawankarki23 - yes, I saved and both manual and scheduled refresh were successful.
- pawankarki231 year agoFrequent Visitor
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])inSourceAll 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.