Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"]])))