Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Execute VSTS/TFS Queries in PowerBI through REST APIs
(CollectionUrl as text, TeamProject as text, QueryGuid as text)=>
let
result = Json.Document(Web.Contents(CollectionUrl,
[
RelativePath=TeamProject & "/_apis/wit/wiql/" & QueryGuid,
Query=[#"api-version" = "1.0"]
]))
in
result
(CollectionUrl as text, WorkItemsIdList as list, Fields as list, CurrentOffset as number, ItensPerRequest as number, WorkItemsCount as number, WorkItemsTable as table) =>
let
//Get the next 'ItensPerRequest' itens
idsRangeList = List.Range(WorkItemsIdList, CurrentOffset, ItensPerRequest),
//Get the workitems
workItems = GetWorkitems(CollectionUrl, idsRangeList, Fields)
in
if CurrentOffset < WorkItemsCount
then @GetAllWorkitems(CollectionUrl, WorkItemsIdList, Fields, (CurrentOffset+ItensPerRequest), ItensPerRequest, WorkItemsCount, WorkItemsTable & workItems)
else WorkItemsTable
(queryResult as record)=>
let
columns = queryResult[columns],
#"Converted to Table" = Table.FromList(columns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
queryColumns = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"referenceName"}, {"referenceName"}),
fieldsList = queryColumns[referenceName]
in
fieldsList
(CollectionUrl as text,TeamProject as text, QueryPath as text) =>
let
result = Json.Document(Web.Contents(CollectionUrl,
[
RelativePath=TeamProject & "/_apis/wit/queries/" & QueryPath,
Query=[#"api-version" = "2.2"]
])),
Source = result[id]
in
Source
(queryResult as record) =>
let
workItems = queryResult[workItems],
#"Converted to Table" = Table.FromList(workItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
workItemIds = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"WorkitemId"}),
ids = workItemIds[WorkitemId]
in
ids
(CollectionUrl as text, Ids as list, Fields as list) =>
let
fieldsString = Text.Combine(Fields,","),
//Convert the Ids list to text and create a string with all ids separeted by a comma
idsStringList = List.Transform(Ids, each Number.ToText(_)),
idsString = Text.Combine(idsStringList,","),
wi = Json.Document(Web.Contents(CollectionUrl,
[
RelativePath="/_apis/wit/WorkItems",
Query=[ids=idsString , fields=fieldsString]
] )),
value = wi[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "rev", "fields", "url"}, {"id", "rev", "fields", "url"}),
#"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", Fields)
in
#"Expanded Column1.fields"
let
queryId = GetQueryId(CollectionUrl, QueryTeamProjectName, QueryPath),
queryResult = ExecuteWIQ(CollectionUrl, QueryTeamProjectName, queryId),
fieldList = GetFields(queryResult),
idList = GetWorkitemIds(queryResult),
//Control Parameters
countOfIds = List.Count(idList),
ItensPerRequest = 200,
WorkItemsTable = #table(type table [],{}),
Result = GetAllWorkitems(CollectionUrl, idList, fieldList, 0, ItensPerRequest, countOfIds, WorkItemsTable),
//Workaround para erro => Formula.Firewall: Query 'MyQuery' (step 'Result') references other queries or steps,
// so it may not directly access a data source. Please rebuild this data combination
workaround = 1
in
Result
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment