Forum Discussion

rudivonstaden's avatar
rudivonstaden
Frequent Visitor
6 years ago

Analizar JSON desde la API de ZenHub

Estoy intentando analizar JSON de una API ZenHub en una tabla. He creado una función, que llamaría a la API para cada fila de una tabla. Sin embargo, el JSON es relativamente complejo y estoy luchand...
rudivonstaden's avatar
rudivonstaden
Frequent Visitor
6 years ago

Me las arreglé para averiguarlo. Esta es mi llamada a la 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"

Y tengo una consulta que genera una lista y luego llama a la función para cada problema de la lista (con retraso para evitar la limitación de velocidad).

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"