Analizar JSON desde la API de ZenHub

[rudivonstaden]

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 luchando para que funcione. Se ve así:

[
    {
        "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"
    }
]

La función para llamar a la API que tengo en la medida en que esto:

(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

Lo que me gustaría es obtener una nueva fila para cada registro, con las columnas "tiempo", "movido de" y "movido a". Sólo probando en un problema (invocado desde la función), tengo esto. Obviamente no funciona, pero puede ayudar a entender lo que estoy tratando de conseguir.

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

¿Alguna idea?

[rudivonstaden]

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"