how to iterate the data of several sheets of a web page?

[emadrigals]

I am making a connection to an API that has several sheets, I was able to make the code to connect to a page with 200 records, however I need to extract the result of all possible sheets, for example, iterate 3 or 4 sheets depending on the number that is choose in the parameter.

Link power bi = Archive Power BI Example 

let
    body = "{
        ""username"" : " & username & ",
        ""password"" : " & password & ",
        ""storeId""  : " & storeId & "
     }",
    content = "{
        ""pagina"" : 1,
        ""cantidad_ordenes""  : " & cantidad_ordenes & ",
        ""desde_fecha"" : " & desde_fecha & ", 
        ""hasta_fecha"" : " & hasta_fecha & ", 
        ""estado_orden""  : " & estado_orden & "
     }",
    getToken = Json.Document(
        Web.Contents(
            authUrl,
            [
                Headers=[#"Content-Type"="application/json"],
                Content=Text.ToBinary(body)
            ]
        )
    ),
    token = getToken[token],
    authToken = "bearer " & token,
    urlRequest = path,
    GetData = (pagina as number) => 
        let
            content = "{
                ""pagina"" : " & Number.ToText(pagina) & ",
                ""cantidad_ordenes""  : " & cantidad_ordenes & ",
                ""desde_fecha"" : " & desde_fecha & ", 
                ""hasta_fecha"" : " & hasta_fecha & ", 
                ""estado_orden""  : " & estado_orden & "
             }",
            results = 
                Json.Document(
                    Web.Contents(
                        urlRequest,
                        [
                            Headers = [Authorization=authToken,#"Content-Type"="application/json"],
                            Content = Text.ToBinary(content)
                        ]
                    )
                ),
            ordenes = results[ordenes],
            data = ordenes[data]
        in
            data,
    pagina = 1,
    paginas = List.Generate(
        () => 
            let
                resultados = GetData(pagina),
                nextPage = resultados[next_page]
            in 
                [nextPage, pagina + 1, resultados[total_pages]],
        (state) => state[0] < state[2],
        (state) => 
            let
                resultados = state,
                nextPage = resultados[0]
            in 
                [nextPage, resultados[1] + 1, resultados[2]]
    ),
    data = List.Transform(paginas, each _[0])
in
    data

let
    dataUnpivoted = List.Transform(data, each Table.FromRecords(_, Record.FieldNames(_))),
    result = Table.Combine(dataUnpivoted)
in
    result

[ams1 · Accepted solution]

Hi,

Below should work, now PLEASE remove the json file 😀:

let 
    body = "{
        ""username"" : " & username & ",
        ""password"" : " & password & ",
        ""storeId""  : " & storeId & "
    }",
    getToken = Json.Document(
        Web.Contents(
            authUrl,
            [
                Headers=[#"Content-Type"="application/json"],
                Content=Text.ToBinary(body)
            ]
        )
    ),
    token = getToken[token],
    authToken = "bearer " & token,
    urlRequest = path,
    // ==================================================================
    getNextPage = (lastPage) => 
        let
            GetPage = (pagina as number) =>
                let
                    content = "{
                        ""pagina"" : " & Number.ToText(pagina) & ",
                        ""cantidad_ordenes""  : " & cantidad_ordenes & ",
                        ""desde_fecha"" : " & desde_fecha & ", 
                        ""hasta_fecha"" : " & hasta_fecha & ", 
                        ""estado_orden""  : " & estado_orden & "
                    }",
                    results = 
                        Json.Document(
                            Web.Contents(
                                urlRequest,
                                [
                                    Headers = [Authorization=authToken,#"Content-Type"="application/json"],
                                    Content = Text.ToBinary(content)
                                ]
                            )
                        ),
                    ordenes = results[ordenes]
                in
                    ordenes,
            nextPage = if lastPage = null 
                       then GetPage(1) 
                       else if lastPage[pagina_actual][0] < lastPage[paginas_totales][0]
                            then GetPage(lastPage[pagina_actual][0] + 1)
                            else null
        in
            nextPage,
    paginas = List.Generate(
        // initial
        () => getNextPage(null),
        // condition
        (lastPage) => lastPage <> null,
        //next
        (lastPage) => getNextPage(lastPage)
    ),
    listOfPages = List.Transform(paginas, each Record.ToTable(_[data])),
    #"Converted to Table" = Table.FromList(listOfPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Value"}, {"Value"}),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Expanded Column1", "Value", {"checkoutMods", "cliente", "codigo_autorizacion", "codigo_metodo_pago", "correo", "costo_shipping", "costo_total_shipping", "cuponTipo", "cuponUsado", "detalle_envio", "detalles", "direcciones", "es_anonimo", "estado_orden", "estado_pago", "fecha_orden", "identificacion", "ip_origen", "medio_pago", "moneda", "moneda_fe", "monto_impuestos", "observaciones", "orderGiftpointsUsed", "orderId", "order_trackingCode", "recoger_sucursal", "sucursal", "tasa_impuesto_shipping", "telefono_fijo", "telefono_movil", "tipo_envio", "total", "wish_id"}, {"checkoutMods", "cliente", "codigo_autorizacion", "codigo_metodo_pago", "correo", "costo_shipping", "costo_total_shipping", "cuponTipo", "cuponUsado", "detalle_envio", "detalles", "direcciones", "es_anonimo", "estado_orden", "estado_pago", "fecha_orden", "identificacion", "ip_origen", "medio_pago", "moneda", "moneda_fe", "monto_impuestos", "observaciones", "orderGiftpointsUsed", "orderId", "order_trackingCode", "recoger_sucursal", "sucursal", "tasa_impuesto_shipping", "telefono_fijo", "telefono_movil", "tipo_envio", "total", "wish_id"})
in
    #"Expanded Value"

[emadrigals]

Thank you a lot, you are awesone...

[ams1]

Hi,

It would be of GREAT help if you can post here the server API response as plain text -> convert the results step to text instead of Json, hide/delete the confidential things and paste the text here.

Use below  to get the API response as plain text:

let body = "{
    ""username"" : " & username & ",
    ""password"" : " & password & ",
    ""storeId""  : " & storeId & "
 }",
 content = "{
    ""pagina"" : " & pagina & ",
    ""cantidad_ordenes""  : " & cantidad_ordenes & ",
    ""desde_fecha"" : " & desde_fecha & ", 
      ""hasta_fecha"" : " & hasta_fecha & ", 
    ""estado_orden""  : " & estado_orden & "
 }",
 getToken = Json.Document(
     Web.Contents(
         authUrl,
         [
             Headers=[#"Content-Type"="application/json"],
             Content=Text.ToBinary(body)
         ]
     )
 ),
 token = getToken[token],
 authToken = "bearer " & token,
 urlRequest = path,
 // ===============================================
 results = 
    Text.FromBinary( // <--- Note i changed here so that you can see the API response
        Web.Contents(
            urlRequest,
            [
                Headers = [Authorization=authToken,#"Content-Type"="application/json"],Content = Text.ToBinary(content)
                
            ]
        )
    )
in
    results

Again, don't forget to hide the confidential data from the response if you're pasting here.

What I need you to please confirm is if the API response looks something like:

{
   "ordenes" : {
       "data" : {
         // This would be the data you want
       }
   }
   "next_page": 2,
   "total_pages": 3,

}

[ams1]

Hi,

First of all I want to say I like how clean the code looks - identation etc. 😀

Questions:

1. What is the error you are getting? -> I doubt that the first query from your code "compiles" (I see 2 x queries in your code, the first one doesn't have a valid record defined...)

2. What does the API response of the first page look like? From your query i see only next_page and total_pages keys, but no data:

{
   "next_page": 2,
   "total_pages": 3,
   ...
   // BUT which key holds the actual data you want?
}

[emadrigals]

Thank you very much, is this functional for you?

[emadrigals]

Thank you for taking the time to answer, thank you very much in advance as this has given me a headache, if you see my power bi file I have a query with the name "Orders by Date", in that file I have managed to extract the information but only for the first tab of the web, as you can see for the chosen parameters there are 3, but they could be more or less depending on the selection of dates. I need to add an iteration and by the way combine all the tabs, to be able to extract all the records.
I share my first code.

let body = "{
    ""username"" : " & username & ",
    ""password"" : " & password & ",
    ""storeId""  : " & storeId & "
 }",
 content = "{
    ""pagina"" : " & pagina & ",
    ""cantidad_ordenes""  : " & cantidad_ordenes & ",
    ""desde_fecha"" : " & desde_fecha & ", 
      ""hasta_fecha"" : " & hasta_fecha & ", 
    ""estado_orden""  : " & estado_orden & "
 }",
 getToken = Json.Document(
     Web.Contents(
         authUrl,
         [
             Headers=[#"Content-Type"="application/json"],
             Content=Text.ToBinary(body)
         ]
     )
 ),
 token = getToken[token],
 authToken = "bearer " & token,
 urlRequest = path,
 results = 
    Json.Document(
        Web.Contents(
            urlRequest,
            [
                Headers = [Authorization=authToken,#"Content-Type"="application/json"],Content = Text.ToBinary(content)
                
            ]
        )
    ),
    ordenes = results[ordenes],
    data = ordenes[data],
    #"Convertido en tabla" = Record.ToTable(data)

in
    #"Convertido en tabla"