Iterate through API and extract fields in Power Query in Power BI

[shadyman]

I’m trying to get all work items related to a Visual Studio Team Services board. I’m surprised there is no “Get all work items” query in the API that Microsoft has published here: https://www.visualstudio.com/en-us/docs/integrate/api/wit/work-items
So therefore I made a custom query in VSTS where I get all work items from current sprint.
The problem is that Power BI shows the result as following:

This is sadly not good enough as I have to Navigate into List => Convert to Table => Expand columns and find out that the only thing it shows is ID and URL as this:

I need to get into the URL and grab hold of the fields in each and every URL like this:

But this doesn’t seem to be that simple. It is not adequate for me to specify the ID for each element like in the example as this is not scalable:
https://fabrikam-fiber-inc.visualst...wit/workitems?ids=297,299,300&api-version=1.0
I need to iterate on the id of all the elements, combine/concatenate it with the URL at the end in the query so I can access the underlying fields. I have tried to do this with partial success:

If I click on “Record” it gives me the fields within the specified work item through its ID. This is all good, but I am not able to increment the number. Only the first row is successful, and all other are left as “null”.

I have tried to write some Power Query and looked at the example given from DataChant:
Iterate over dynamic Web API Pages with Power Query - How to resolve Cursor-Based Pagination - DataChant
This is almost the exact same thing as I am trying to do, but I am not experienced in Power Query.
How would I go by fixing this?

This is my query:

let    Source = Json.Document(Web.Contents("https://dxno.visualstudio.com/DefaultCollection/DX/_apis/wit/wiql/222fd0c3-5ae6-4fad-86e8-23b0fa5fc6e7?api-version=2.2")),
    workItems = Source[workItems],
    #"Converted to Table" = Table.FromList(workItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "url"}, {"Column1.id", "Column1.url"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.url"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.id", "itemNumber"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"itemNumber", Int64.Type}}),
    itemNumber = #"Changed Type"[itemNumber],
    
    min = List.Min(itemNumber),
    max = List.Max(itemNumber),
    Variance = max-min,
    CountOfLoops = Number.ToText(Variance),


    iterations = Variance,
    url = Text.Combine({"https://dxno.visualstudio.com/DefaultCollection/_apis/wit/workItems/", CountOfLoops}, ""), 




FnGetOnePage =
  (url) as record =>
   let
    Sourcie = Json.Document(Web.Contents(url)),
    fields = try Sourcie[fields] otherwise null,
    res = [Data=fields]
   in
    res,
 
 GeneratedList =
  List.Generate(()=>[i=0, res = FnGetOnePage(url)], 
   each [i]<iterations and="" [res]<="">null,
   each [i=[i]+1, res = FnGetOnePage([res])],
   each [res][Data])
in

    GeneratedList

</iterations>