ExecuteWIQ

## 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