API call datelimit

[Anonymous]

H! I'm relatively new to Power BI and would need some help. I have an API that responds with conversation history, and in the call I have to fill in the start and end date but the problem is that the API can only take 32 days at a time, and I need several months. In addition, I want it to be dynamic, so that I can update and get the latest day's data every day. How should I do?

Source = Json.Document(Web.Contents("https://XX/api/external/statistics/v1?from_date=2020-09-01&to_date=2020-09-30", [Headers=[Authorization="XXX"]])),

Thanks!

[lbendlin]

1. In Power Query create a data source that lists the last x periods of 32 days  (List.Generate etc)

2. parameterize your query to the API by adding a custom column to the above table that fetches web.contents() for each of the dynamically generated URLs.

Something like this:

let
    Source = List.Generate(()=>DateTime.LocalNow(),each _ > Date.AddDays(DateTime.LocalNow(),-600),each Date.AddDays(_, -32)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"From"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"From", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "To", each Date.AddDays([From],-31)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "URL", each "https://XX/api/external/statistics/v1?from_date=" & Date.ToText([From],"yyyy-MM-dd") & "&to_date=" & Date.ToText([To],"yyyy-MM-dd"))
in
    #"Added Custom1"

[Anonymous]

Hi! Thank you @lbendlin 

I tried to solve it but I got stuck, my code looks like this:

let
    Source = List.Generate(()=>DateTime.LocalNow(),each _ > Date.AddDays(DateTime.LocalNow(),-600),each Date.AddDays(_, -32)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"From"}, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "To", each Date.AddDays([From],-31)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "URL", each  Json.Document(Web.Contents("https://XXX/api/external/statistics/v1?from_date=" & Date.ToText([From],"yyyy-MM-dd") & "&to_date=" & Date.ToText([To],"yyyy-MM-dd")&"environment=live&language=sv-SE&key=conversation_count",[Headers=[Authorization="XXXXX"]])))
in
    #"Added Custom1"

But I get this error, what am I doing wrong? 

Expression.Error: We cannot convert the value #datetime(2020, 1, 15, 17, 18, 27.0735444) to type Date.
Details:
    Value=2020-01-15 17:18:27
    Type=[Type]

The API also respons with dates 🙂 

/Sophia 😀

[lbendlin · Accepted solution]

 #"Added Custom1" = Table.AddColumn(#"Added Custom", "URL", each  Json.Document(Web.Contents("https://XXX/api/external/statistics/v1?from_date=" & Date.ToText(Date.From([From]),"yyyy-MM-dd") & "&to_date=" & Date.ToText(Date.From([To]),"yyyy-MM-dd")&"environment=live&language=sv-SE&key=conversation_count",[Headers=[Authorization="XXXXX"]])))