Parse JSON from ZenHub API

[rudivonstaden]

I am trying to parse JSON from a ZenHub API into a table. I have created a function, which would call the API for each row in a table. The JSON is relatively complex though, and I'm struggling to get it to work. It looks like this:

[
    {
        "user_id": 1696560,
        "type": "transferIssue",
        "created_at": "2020-05-23T16:18:50.584Z",
        "from_pipeline": {
            "name": "In Progress"
        },
        "to_pipeline": {
            "name": "Ready to Deploy"
        },
        "workspace_id": "53653445a72d3471720a15cb"
    },
    {
        "user_id": 1696560,
        "type": "transferIssue",
        "created_at": "2020-05-22T10:11:44.084Z",
        "from_pipeline": {
            "name": "Up Next"
        },
        "to_pipeline": {
            "name": "In Progress"
        },
        "workspace_id": "53653445a72d3471720a15cb"
    },
    {
        "user_id": 1696560,
        "type": "transferIssue",
        "created_at": "2020-05-19T07:51:54.743Z",
        "from_pipeline": {
            "name": "New Issues"
        },
        "to_pipeline": {
            "name": "Up Next"
        },
        "workspace_id": "53653445a72d3471720a15cb"
    }
]

The function to call the API I have so far as this:

(issueNumber as text) =>
let
    Source = Json.Document(Web.Contents("https://api.zenhub.com/p1/repositories/<repository_number>/issues/" & issueNumber & "/events", [Headers=[#"X-Authentication-Token"="<token>"]]))
in
    Source

What I would like is to get a new row for each record, with the columns "time", "moved from", and "moved to". Just testing on one issue (invoked from the function), I have this. It obviously doesn't work, but may help understanding what I'm trying to get.

let
    Source = events("4386"),
    Source1 = Table.FromRecords(Source{1}),  // this should probably be a for loop: for each record in Source
    AddTime = Table.AddColumn(Source1, "time", each Source1[created_at]),
    AddFromPipeline = Table.AddColumn(Source1, "moved from", each Source1[from_pipeline]),
    AddToPipeline = Table.AddColumn(Source1, "moved to", each Source1[to_pipeline])
in
    AddToPipeline

Any ideas?

[rudivonstaden]

I managed to figure it out. This is my call to the API:

(issueNumber as text) =>
let
    Source = Json.Document(Web.Contents("https://api.zenhub.com/p1/repositories/<repository_number>/issues/" & issueNumber & "/events", [Headers=[#"X-Authentication-Token"="<token>"]])),
    Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"created_at", "from_pipeline", "to_pipeline"}, {"time", "Column1.from_pipeline", "Column1.to_pipeline"}),
    #"Expanded Column1.from_pipeline" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.from_pipeline", {"name"}, {"moved from"}),
    #"Expanded Column1.to_pipeline" = Table.ExpandRecordColumn(#"Expanded Column1.from_pipeline", "Column1.to_pipeline", {"name"}, {"moved to"}),
    #"Changed Column Types" = Table.TransformColumnTypes(#"Expanded Column1.to_pipeline",{{"time", type datetime}, {"moved from", type text}, {"moved to", type text}})
in
    #"Changed Column Types"

And I've got a query that generates a list and then calls the function for each issue in the list (with delay to avoid rate limiting).

let
    Source = List.Numbers(4000,50,1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Events", each Function.InvokeAfter(()=>#"ZenHub events"([Column1]), #duration(0,0,0,1))),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Events"}),
    #"Expanded Events" = Table.ExpandTableColumn(#"Removed Errors", "Events", {"time", "moved from", "moved to"}, {"time", "moved from", "moved to"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Events",{{"Column1", "Issue Number"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([moved from] <> null and [moved to] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"time", type datetime}, {"Issue Number", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Issue Number",Order.Ascending},{"time", Order.Ascending}})
in
    #"Sorted Rows"