Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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"
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors