none
Iterate through API and extract fields in Power Query in Power BI RRS feed

  • Question

  • 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.visualstudio.com/DefaultCollection/_apis/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]null,   each [i=[i]+1, res = FnGetOnePage([res])],   each [res][Data])in    GeneratedList

    <iterations [res]<="" and="" style="color:#222222;font-family:Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif;font-size:13px;line-height:normal;"></iterations>
    Wednesday, August 24, 2016 10:01 PM

Answers

  • Having difficulties understanding your problem/approach. So let's try it the other way around:

    If you add a column to the table above that stated the ID and URL like this:

    = Json.Document(Web.Contents( [Column1.URL] )) [workItems]

    It should return the workItems of each site.

    You can then further transform it to a table, using Table.FromRows ... or combine it in one step already like this:

    Content = Table.AddColumns(NameOfThePreviousStep, "WorkItems", each Table.FromRows(Json.Document(Web.Contents( [Column1.URL] )) [workItems]) )

    Then expand that table column.

    How far is that result from your desired result?


    Imke Feldmann TheBIccountant.com


    Tuesday, August 30, 2016 7:25 AM
    Moderator

All replies

  • Having difficulties understanding your problem/approach. So let's try it the other way around:

    If you add a column to the table above that stated the ID and URL like this:

    = Json.Document(Web.Contents( [Column1.URL] )) [workItems]

    It should return the workItems of each site.

    You can then further transform it to a table, using Table.FromRows ... or combine it in one step already like this:

    Content = Table.AddColumns(NameOfThePreviousStep, "WorkItems", each Table.FromRows(Json.Document(Web.Contents( [Column1.URL] )) [workItems]) )

    Then expand that table column.

    How far is that result from your desired result?


    Imke Feldmann TheBIccountant.com


    Tuesday, August 30, 2016 7:25 AM
    Moderator
  • Attempting same operation, however the result set returned by the request appears to be invalid JSON:

    DataFormat.Error: We found extra characters at the end of JSON input.
    Details:
        Value=
        Position=4

    Running query via browser returns data as expected:

    {"queryType":"flat","queryResultType":"workItem","asOf":"2017-08-16T19:58:17.607Z","columns":[{"referenceName":"System.Id","name":"ID","url":"https://.....

    Wednesday, August 16, 2017 8:04 PM