Forum Discussion

rudivonstaden's avatar
rudivonstaden
Frequent Visitor
6 years ago

Parse JSON from ZenHub API

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...
Need Help
rudivonstaden's avatar
rudivonstaden
Frequent Visitor
6 years ago

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"