cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anderssons
Frequent Visitor

API call datelimit

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!

1 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"]])))

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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"

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 😀

 #"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"]])))

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors