Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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"]])))
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"]])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.