Power BI: How to generate a table of daily web traffic reports? Help?

[rsderby68]

I pull data from a digital site via an API into Power BI for reporting.  The API has an analytics endpoint for a web traffic report, but it is ONLY an aggregate report, as in it won't make one call and separate it into individual days.  I can call (below) to show the traffic for any particular day.  I would like to write a query to use this API call to generate a table where each row is the traffic for a day starting at 2022-05-15 until whatever the current day is.  So a row for 05-15, 05-16, 05-18, until the current date.  

Anyone have a suggestion?  

https://api.somewhere.tv/analytics?type=traffic&from=2022-05-15&to=2022-05-15

[ppm1 · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    StartDate = #date(2022,5,15),
    EndDate = Date.From(DateTime.LocalNow()),
    DateList = List.Dates(StartDate, Duration.TotalDays(EndDate - StartDate) + 1, #duration(1,0,0,0)),
    Custom1 = List.Transform(DateList, each Date.ToText(_, "yyyy-MM-dd")),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateAsText"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateAsText", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "WebCall", each Web.Contents("https://api.somewhere.tv/analytics?type=traffic&from=" & [DateAsText] & "&to=" &  [DateAsText]))
in
    #"Added Custom"

Pat

[rsderby68]

Pat, did I mention you are my favorite person?  Thanks!