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