Loop in M query

[DLX_Ares_SP]

Hi,

I am trying to make a loop with an M query, but I can't find the solution.

First, why I want to do it: I am getting information via API from one of the platforms we use in our company. I have no problem with this query, but there is a limitation on the information I can get in each query. So, in one of the queries, if I want to get the complete history of data, it exceeds the amount of the info I can get at once.

I think it could be solved using the following approach: Since I can bound the query with start and end date of the data I request. I have created a 'calendar' query, called Date_ranges, in which I have created all the days between the two dates of my interest. Then I have reduced it to two columns: one with the first date of the month and one with the last day of the month. I have the idea of running the query month by month and join all the results:

let
    Origen = Table.FromColumns(
    {
        {StartDate},
        {Date.From(DateTime.LocalNow())}
    },
    type table [StartDate = date, EndDate = date]
),
    #"Personalizada agregada" = Table.AddColumn(Origen, "Dates", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Se expandió Personalizado" = Table.ExpandListColumn(#"Personalizada agregada", "Dates"),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Se expandió Personalizado",{{"Dates", type date}}),
    #"Columnas quitadas" = Table.RemoveColumns(#"Tipo cambiado",{"StartDate", "EndDate"}),
    #"Inicio del mes insertado" = Table.AddColumn(#"Columnas quitadas", "StartOfMonth", each Date.StartOfMonth([Dates]), type date),
    #"Personalizada agregada1" = Table.AddColumn(#"Inicio del mes insertado", "EndOfMonth", each Date.EndOfMonth([Dates]), type date),
    #"Columnas quitadas1" = Table.RemoveColumns(#"Personalizada agregada1",{"Dates"}),
    #"Duplicados quitados" = Table.Distinct(#"Columnas quitadas1", {"StartOfMonth"})
in
    #"Duplicados quitados"

Up to this step the result is satisfactory as expected:

Now it is time to perform the loop, which I am not able to define correctly, is the actual query without the loop is as follows:

let
    StartDate = "",
    EndDate = "",

    Source = Json.Document(Web.Contents("https://api.aceproject.com/?fct=login&accountid=*********&username=data_source&password="&Password&"&browserinfo=NULL&language=NULL&format=JSON")),
    results = Source[results],
    results1 = results{0},
    GUID = results1[GUID],
    Source2 = Xml.Tables(Web.Contents("https://api.aceproject.com/?fct=gettimereport&guid=" & GUID & "&view=1&otherview=NULL&timesheetlineid=NULL&projectid=NULL&filtermyworkitems=False&filtertimecreatoruserid=NULL&filtertimecreatorusergroupid=NULL&filtertimetypeid=NULL&filtertimelevel=0&filtertimestatus=NULL&filterdatefrom="& StartDate &"&filterdateto="& EndDate &"&filterprojecttypeid=NULL&filterclientid=NULL&filtertaskid=NULL&filtertaskgroupid=NULL&filtertasktypeid=NULL&countonly=False&isshowtotalsonly=False&fieldstodisplay=NULL&sortorder=NULL&asynccall=False&asynccallid=NULL&exportdomainevaleur=NULL&exporttype=NULL&exportdelimiter=NULL&exportdecimalsymbol=NULL&exportlcid=0&exportonscreencolumnsonly=True&exportview=0&exportviewother=NULL&exportfieldstodisplay=NULL&exportremovehtmlonly=True&exportenablefilterxls=False&format=xml")),
    Table = Source2{0}[Table]
in
    Table

The idea would be to make a loop that takes the data from the Date_ranges query row by row. Using the start and end day of the month as a variable in each iteration and then appending the API output, do it until the last row of the Data_ranges query.

Can anyone help on this? is it feasible?

Thanks in advance

[AlienSx]

Hi, @DLX_Ares_SP make a custom function out of your "actual query without the loop" with dates as parameters, transform dates using Date.ToText like this:

my_little_query = (sd as date, ed as date) =>
  let 
    StartDate = Date.ToText(sd),
    EndDate = DateToText(ed),
    <the rest of your original query>

 use optional parameters of Date.ToText to control it's text output. Then you may use Table.AddColumn in your Date_ranges query to feed this custom function with StartOfMonth and EndOfMonth to get a column with tables: 

Table.AddColumn(
  #"Duplicados quitados", "tables", 
  (x) => my_little_query(x[StartOfMonth], x[EndOfMonth])
)

Then you may expand this new column with tables the way you want.