Execute VSTS/TFS Queries in PowerBI through REST APIs

ExecuteWIQ

(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

GetAllWorkitems

(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

GetFields

(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

GetQueryId

(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

GetWorkitemIds

(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

GetWorkitems

(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"

MyQuery

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