Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Iterating over start and end dates for multiple API calls

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.

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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!

View solution in original post

Anonymous
Not applicable

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]

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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!

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?

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
Not applicable

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 query2018-01-18 13 00 51.png

 

 

And below is the code I am trying to use for the new solution, ie multiple lists2018-01-18 13 02 14.png

 

 

and getting following error2018-01-18 13 02 44.png

 

 

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 get2018-01-18 13 22 54.png

 

 

 

below is how the data is stored2018-01-18 13 21 51.png

 

 

Apologies for such a long post.

Regards

Anonymous
Not applicable

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
Not applicable

Thank you so much mate, its working now 🙂

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.