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
    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!

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]