Rest API multiple times with param

[edmar_machado]

Hi I am beginner using API sources, I have a problem to get of data from this url api, i have 3 parameters start, end and offset.

I got the start and end date of list, ex:

START date   END date

25/10/2022   26/10/2022

I was using this code

let

    Consulta2 = List.Dates(#date(2021, 12, 1), Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-(#date(2021, 12, 1)))), #duration(1, 0, 0, 0)),

    #"Convertido para Tabela" = Table.FromList(Consulta2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Personalização Adicionada" = Table.AddColumn(#"Convertido para Tabela", "Personalizar", each Date.AddDays([Column1],1)),

    #"Colunas Renomeadas" = Table.RenameColumns(#"Personalização Adicionada",{{"Column1", "StartDate"}, {"Personalizar", "EndDate"}}),

    #"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Renomeadas",{{"StartDate", type datetime}, {"EndDate", type datetime}}),

    #"Função Personalizada Invocada" = Table.AddColumn(#"Tipo Alterado", "fnTeste", each fnTeste([StartDate], [EndDate])),

    #"fnTeste Expandido" = Table.ExpandRecordColumn(#"Função Personalizada Invocada", "fnTeste", {"offset", "limit", "has_more", "data"}, {"offset", "limit", "has_more", "data"})

in

    #"fnTeste Expandido"

the idea is for example start date 01/03/2022 , enddate 02/03, the field has more equal true means that there is more data and i need to get the more data using offset.

the point is I have to put offset param to ensure all data get of this range of start and end date.

I tried to invoke the function using start and enddate, but i dont find a solution the same time when i invoke a function get the offset param as weel.

how can I do that? does anyone could help me?

i was using list.generate, but I need somehow the same time when i invoke custom with start and end date put the offset to get all data to each interval of data.

= List.Generate(

        () => [offset = 100, estoque = FnOffSet(0)],

        each not List.IsEmpty ([estoque][data]),

        each [offset = [offset] + 100 , estoque = FnOffSet([offset])],

        each [estoque]

    )

fnTeste

= (startDate as datetime, endDate as datetime) =>

let

    Fonte = Json.Document(

        Web.Contents(

            "https://api.eyemobile.com.br",

            [RelativePath="/v1/transactions" & "?limit=100&"&

    "offset="&Number.ToText(offSet)&"&"&"start="& Date.ToText(DateTime.Date(startDate),"yyyy-MM-dd")&"T10:00:00Z&" &

    "end="& Date.ToText(DateTime.Date(endDate),"yyyy-MM-dd")&"T03:00:00Z&" & "cancelled=false&serialized_types=0,4,17,18",

    Headers=[#"Content-Type"="application/json", #"X-EYEMOBILE-ACCESS-KEY"=AcessKey, #"X-EYEMOBILE-SECRET-KEY"=SecretKey]]))

in

    Fonte

[edmar_machado]

Hi thanks @Anonymous thanks for your fast replay I tried to reach out the solution and I found it.

= (startDate as datetime, endDate as datetime) =>
let
Source = List.Generate(
()=> [Result =
try Json.Document(
Web.Contents("https://api.eyemobile.com.br",
[RelativePath="/v1/transactions" & "?limit=100&"&
"offset="&Number.ToText(OffSet)&"&"&"start="& Date.ToText(DateTime.Date(startDate),"yyyy-MM-dd")&"T10:00:00Z&" &
"end="& Date.ToText(DateTime.Date(endDate),"yyyy-MM-dd")&"T03:00:00Z&" & "cancelled=false&serialized_types=0,4,17,18",
Headers=[#"Content-Type"="application/json", #"X-EYEMOBILE-ACCESS-KEY"=AcessKey, #"X-EYEMOBILE-SECRET-KEY"=SecretKey]]))
otherwise null, OffSet = 0],
each not List.IsEmpty ([Result][data]),
each [Result =
try Json.Document(
Web.Contents("URL",
[RelativePath="/v1/transactions" & "?limit=100&"&
"offset="&Number.ToText(OffSet)&"&"&"start="& Date.ToText(DateTime.Date(startDate),"yyyy-MM-dd")&"T10:00:00Z&" &
"end="& Date.ToText(DateTime.Date(endDate),"yyyy-MM-dd")&"T03:00:00Z&" & "cancelled=false&serialized_types=0,4,17,18",
Headers=[#"Content-Type"="application/json", #"X-EYEMOBILE-ACCESS-KEY"=AcessKey, #"X-EYEMOBILE-SECRET-KEY"=SecretKey]]))
otherwise null, OffSet = [OffSet] + 100],
each [Result]
)
in
Source

I created incremental refresh, but I've had a issue when i publish on power bi service

{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"We cannot apply field access to the type Null."}}],"exceptionCulprit":1}}}

C

I suspect the problem its here otherwise null, code above.

is there any way to prevent this when i call function to ignore dates that i dont have information?

thanks 

[Anonymous]

Hi @edmar_machado ,

Please kindly refer to

Solved: Call a REST API multiple times using Power Query - Microsoft Power BI Community

Or you can create a function from your query using this syntax:

let functionName = (param1 as datatype) =>

let

 Source = Json.Document(Web.Contents("https:// MY API CALL" & param1).

in

 Source

in functionName

You can then create a table with your parameters and then add a custom column to your table that calls/invokes your function

You can see an example here M Parameterized Function for Power Query

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.