Forum Discussion
Iterating over start and end dates for multiple API calls
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 URLI 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!
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]
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
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]
Thank you so much mate, its working now :)