I am trying to get the Devops Board data into PowerBI using the below Query. The only issue I am facing here is that my query only works if I use FirstN option and for some reason if I use am passing a value over 200, POWER BI is throwing an error and query stop working.
My method: Fetch all the workitemIds of the baord and then pass it as a parameter to get all the Devops Board data value as a Json query and convert it into table.
The below code is working fine for records upto 200, but I need all the data... Tried using .First, etc.. Any help please or any other wokring solution.
* Please do not suggest using Analytics view because it is not giving me custom fields like Board Column which I require the most.
Power Query Code:
ItemIDs1 = OData.Feed("**URL**/_odata/v3.0-preview/WorkItems?" & "$select=WorkItemId" & "&$filter=WorkItemType eq 'Feature' AND State ne 'Removed' AND State ne 'Closed'", null, [Implementation="2.0"]),
ItemIDs = Table.FirstN(ItemIDs1,200),
temp = Table.ToList(ItemIDs, (x)=> Combiner.CombineTextByDelimiter(",")(List.Transform(x, each Text.From(_)))),
IDs = Text.Combine(temp,","),
Source = Json.Document(Web.Contents("https://**URL**/wit/workitems?ids="&IDs&"&$expand=Fields&api-version=5.1")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),
#"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"id", "rev", "fields", "url"}, {"value.id", "value.rev", "value.fields", "value.url"}),
#"Expanded value.fields" = Table.ExpandRecordColumn(#"Expanded value1", "value.fields", {"System.AreaPath", "System.TeamProject", "System.IterationPath", "System.WorkItemType", "System.State", "System.Reason", "System.CreatedDate", "System.CreatedBy", "System.ChangedDate", "System.ChangedBy", "System.CommentCount", "System.Title", "System.BoardColumn", "System.BoardColumnDone", "Microsoft.VSTS.Common.StateChangeDate", "Microsoft.VSTS.Common.Priority", "Microsoft.VSTS.Common.StackRank", "Microsoft.VSTS.Common.ValueArea", "WEF_9DAD8E73BB744DC18359E2BE71F1FE82_Kanban.Column", "WEF_9DAD8E73BB744DC18359E2BE71F1FE82_Kanban.Column.Done"}, {"value.fields.System.AreaPath", "value.fields.System.TeamProject", "value.fields.System.IterationPath", "value.fields.System.WorkItemType", "value.fields.System.State", "value.fields.System.Reason", "value.fields.System.CreatedDate", "value.fields.System.CreatedBy", "value.fields.System.ChangedDate", "value.fields.System.ChangedBy", "value.fields.System.CommentCount", "value.fields.System.Title", "value.fields.System.BoardColumn", "value.fields.System.BoardColumnDone", "value.fields.Microsoft.VSTS.Common.StateChangeDate", "value.fields.Microsoft.VSTS.Common.Priority", "value.fields.Microsoft.VSTS.Common.StackRank", "value.fields.Microsoft.VSTS.Common.ValueArea", "value.fields.WEF_9DAD8E73BB744DC18359E2BE71F1FE82_Kanban.Column", "value.fields.WEF_9DAD8E73BB744DC18359E2BE71F1FE82_Kanban.Column.Done"}),
#"Expanded value.fields.System.CreatedBy" = Table.ExpandRecordColumn(#"Expanded value.fields", "value.fields.System.CreatedBy", {"displayName", "url", "_links", "id", "uniqueName", "imageUrl", "descriptor"}, {"value.fields.System.CreatedBy.displayName", "value.fields.System.CreatedBy.url", "value.fields.System.CreatedBy._links", "value.fields.System.CreatedBy.id", "value.fields.System.CreatedBy.uniqueName", "value.fields.System.CreatedBy.imageUrl", "value.fields.System.CreatedBy.descriptor"}),
#"Expanded value.fields.System.CreatedBy._links" = Table.ExpandRecordColumn(#"Expanded value.fields.System.CreatedBy", "value.fields.System.CreatedBy._links", {"avatar"}, {"value.fields.System.CreatedBy._links.avatar"}),
#"Expanded value.fields.System.CreatedBy._links.avatar" = Table.ExpandRecordColumn(#"Expanded value.fields.System.CreatedBy._links", "value.fields.System.CreatedBy._links.avatar", {"href"}, {"value.fields.System.CreatedBy._links.avatar.href"}),
#"Expanded value.fields.System.ChangedBy" = Table.ExpandRecordColumn(#"Expanded value.fields.System.CreatedBy._links.avatar", "value.fields.System.ChangedBy", {"displayName", "url", "_links", "id", "uniqueName", "imageUrl", "descriptor"}, {"value.fields.System.ChangedBy.displayName", "value.fields.System.ChangedBy.url", "value.fields.System.ChangedBy._links", "value.fields.System.ChangedBy.id", "value.fields.System.ChangedBy.uniqueName", "value.fields.System.ChangedBy.imageUrl", "value.fields.System.ChangedBy.descriptor"}),
#"Expanded value.fields.System.ChangedBy._links" = Table.ExpandRecordColumn(#"Expanded value.fields.System.ChangedBy", "value.fields.System.ChangedBy._links", {"avatar"}, {"value.fields.System.ChangedBy._links.avatar"}),
#"Expanded value.fields.System.ChangedBy._links.avatar" = Table.ExpandRecordColumn(#"Expanded value.fields.System.ChangedBy._links", "value.fields.System.ChangedBy._links.avatar", {"href"}, {"value.fields.System.ChangedBy._links.avatar.href"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded value.fields.System.ChangedBy._links.avatar",{{"count", Int64.Type}, {"value.id", Int64.Type}, {"value.rev", Int64.Type}, {"value.fields.System.AreaPath", type text}, {"value.fields.System.TeamProject", type text}, {"value.fields.System.IterationPath", type text}, {"value.fields.System.WorkItemType", type text}, {"value.fields.System.State", type text}, {"value.fields.System.Reason", type text}, {"value.fields.System.CreatedDate", type datetime}, {"value.fields.System.CreatedBy.displayName", type text}, {"value.fields.System.CreatedBy.url", type text}, {"value.fields.System.CreatedBy._links.avatar.href", type text}, {"value.fields.System.CreatedBy.id", type text}, {"value.fields.System.CreatedBy.uniqueName", type text}, {"value.fields.System.CreatedBy.imageUrl", type text}, {"value.fields.System.CreatedBy.descriptor", type text}, {"value.fields.System.ChangedDate", type datetime}, {"value.fields.System.ChangedBy.displayName", type text}, {"value.fields.System.ChangedBy.url", type text}, {"value.fields.System.ChangedBy._links.avatar.href", type text}, {"value.fields.System.ChangedBy.id", type text}, {"value.fields.System.ChangedBy.uniqueName", type text}, {"value.fields.System.ChangedBy.imageUrl", type text}, {"value.fields.System.ChangedBy.descriptor", type text}, {"value.fields.System.CommentCount", Int64.Type}, {"value.fields.System.Title", type text}, {"value.fields.System.BoardColumn", type text}, {"value.fields.System.BoardColumnDone", type logical}, {"value.fields.Microsoft.VSTS.Common.StateChangeDate", type datetime}, {"value.fields.Microsoft.VSTS.Common.Priority", Int64.Type}, {"value.fields.Microsoft.VSTS.Common.StackRank", Int64.Type}, {"value.fields.Microsoft.VSTS.Common.ValueArea", type text}, {"value.fields.WEF_9DAD8E73BB744DC18359E2BE71F1FE82_Kanban.Column", type text}, {"value.fields.WEF_9DAD8E73BB744DC18359E2BE71F1FE82_Kanban.Column.Done", type logical}, {"value.url", type text}})
in
#"Changed Type"
