Iterating over start and end dates for multiple API calls

[Anonymous]

Hi Community,

I am working on an API call to fetch some data. The API call is a simple get command, with all the query parameters as part of the URL itself.

These query parameters include the information I need plus the dates for which I need the data. these dates are start and and end dates. The API is only returning a month's data in each call, so I would want to run the query multiple times to fetch say a years data.

I have been able to put few things together but I am unble to create the iteration and keep it running.

Since the API call is just the URL, so it is a string. I have created two variables, which are created for start and end dates, as strings, in the same format as API accepts it, and concatenated within the URL.

I am getting the desired results for the specific dates.

let

referenceForEndDate = Date.AddMonths(DateTime.LocalNow()),
day = Number.ToText(Date.Day(referenceForEndDate)),
month = Number.ToText(Date.Month(referenceForEndDate)),
year = Number.ToText(Date.Year(referenceForEndDate)),
enddate = year&"-"&month&"-"&day,

referenceForStartDate = Date.AddMonths(DateTime.LocalNow(),-1),
day1 = Number.ToText(Date.Day(referenceForStartDate)),
month1 = Number.ToText(Date.Month(referenceForStartDate)),
year1 = Number.ToText(Date.Year(referenceForStartDate)),
startdate= year1&"-"&month1&"-"&day1,

    
Source = Json.Document(Web.Contents(
"https://supermetrics.com/api/v1/getData?metrics=frequency%2Cactions&dimensions=Date&maxResults=50
&start-date="
&startdate&
"&end-date="
&enddate&
"&profiles=[MyProfileName]&dataSource=FA&dsUser=[MyUserId]&apiKey=[MyApiKey]
")),
data = Source[data],
data1 = Table.FromRows(data)
in
    data1

Now, If I would want to use this code dynamically , to fetch the data for past 12 months, and keep it running for future.

is it doable? I have seen iterations based on lists, not being an expert on M, I could not think about the way to iterate over two lists( one for start date and one for stop date) and also how to create those lists which are a construction from date into 'string' date as done in above example.

Any direction in this regard would be highly appriciated.

many thanks.

P.S. I had to edit the API access code & account names due to the nature of the data.

[Anonymous · Accepted solution]

Try this M Code:

let
    StartYear = 2016,
    EndYear = 2017,
    Years = Table.FromList({StartYear..EndYear}, Splitter.SplitByNothing(), null,null, ExtraValues.Error),
    AddMonth = Table.AddColumn(Years, "Month", each {1..12}),
    ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
    CreateStartDate = Table.AddColumn(ShowMonths, "StartDate", each #date([Column1],[Month],1), type date),
    CreateEndDate = Table.AddColumn(CreateStartDate, "EndDate", each Date.EndOfMonth([StartDate]), type date),
    #"Changed Type" = Table.TransformColumnTypes(CreateEndDate,{{"StartDate", type text}, {"EndDate", type text}}),
    URL =  Table.AddColumn(#"Changed Type", "URL", each "https://supermetrics.com/api/v1/getData?metrics=frequency%2Cactions&dimensions=Date&maxResults=50&start-date="&[StartDate]&"&end-date="&[EndDate]&"&profiles=[MyProfileName]&dataSource=FA&dsUser=[MyUserId]&apiKey=[MyApiKey]", type text)
in
    URL

I modified some code by Imke Feldman, a brilliant blogger that is amazing with M.

All you need do is set the StartYear and EndYear in the first 2 lines (note, they can be the same if you only need 1 year of data).

This code will create a list of the beginning and end of each month, and store the values as text as StartDate and EndDate respectively.  I then add a column for URL that concatenates everything together.  How you can add another column with the equation....

Json.Document(Web.Contents([URL]))

each row should return back a table with the data for that particular StartDate and EndDate pair.  Then you just need to expand the tables and remove the columns  you don't need anymore.

Hope this helps!

[Anonymous · Accepted solution]

Try this M Code:

let
    StartYear = 2016,
    EndYear = 2017,
    Years = Table.FromList({StartYear..EndYear}, Splitter.SplitByNothing(), null,null, ExtraValues.Error),
    AddMonth = Table.AddColumn(Years, "Month", each {1..12}),
    ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
    CreateStartDate = Table.AddColumn(ShowMonths, "StartDate", each #date([Column1],[Month],1), type date),
    CreateEndDate = Table.AddColumn(CreateStartDate, "EndDate", each Date.EndOfMonth([StartDate]), type date),
    #"Changed Type" = Table.TransformColumnTypes(CreateEndDate,{{"StartDate", type text}, {"EndDate", type text}}),
    URL =  Table.AddColumn(#"Changed Type", "URL", each "https://supermetrics.com/api/v1/getData?metrics=frequency%2Cactions&dimensions=Date&maxResults=50&start-date="&[StartDate]&"&end-date="&[EndDate]&"&profiles=[MyProfileName]&dataSource=FA&dsUser=[MyUserId]&apiKey=[MyApiKey]", type text)
in
    URL

I modified some code by Imke Feldman, a brilliant blogger that is amazing with M.

All you need do is set the StartYear and EndYear in the first 2 lines (note, they can be the same if you only need 1 year of data).

This code will create a list of the beginning and end of each month, and store the values as text as StartDate and EndDate respectively.  I then add a column for URL that concatenates everything together.  How you can add another column with the equation....

Json.Document(Web.Contents([URL]))

each row should return back a table with the data for that particular StartDate and EndDate pair.  Then you just need to expand the tables and remove the columns  you don't need anymore.

Hope this helps!

[wdvro]

Hello,

I was experiencing the same kind of issue as the original poster. 

I applied the code to my case in Power BI Desktop with positive result, all available rows were imported via the API.

However, I encounterd another problem. I saved the report on the cloud service and tried to enable autorefresh. But this isn't possible, following error is displayed:

Something went wrong

This data set contains a dynamic data source. Because dynamic data sources are not refreshed in the Power BI service, this data set is not refreshed. Learn more: https://aka.ms/dynamic-data-sources.
Please try again later or contact support. Please provide the following information when contacting support.

How to resolve this issue?

[gingercat123]

Great Solution!   How do i get StartDate and EndDate in YYYY-MM-DD format please as  api link that i am using accepts date in YYYY-MM-DD format

[Anonymous]

Thanks @Anonymous, thats awesome and is wokring. 

Which has led me to the next bit.

When I was running the code for one instance only, I was getting the results in a table of lists. 

First List(row in the table) got all the headers ( thats how the API is reporting the data).

Secind list got data for second row and so on.

I used Table.FromRows() function to convert it to usable format.

Now, that I am getting output for  say 12 months, I am getting 12 such lists, and each one of them carries similar lists as described above.

Due to some odd reason, the same Table.FromRows() doesnt work on this structure, as says,

Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=Table
Type=Type

This is what is working from single query

And below is the code I am trying to use for the new solution, ie multiple lists

and getting following error

Do I need to run same function here Table.FromRows() one by one on each cell , if yes, how do I do that.

or is there anything else I can do about it.

Below the final list that I get

below is how the data is stored

Apologies for such a long post.

Regards

[Anonymous · Accepted solution]

Hmmm, is there a way you can upload the file so we can have a look?  It's tough to know exactly what's going on.

To answer part of your question, click on the Add Column tab in the ribbon, then click on Add Custom Column.

type this in the formula section of the dialog box:

Table.FromRows([Column1.data])

I'm not exactly sure that's the function you want to be using though.  But at least that will apply the Table.FromRows() function to each list in [Column1.data]

[Anonymous]

Thank you so much mate, its working now 🙂