Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hey,
we are developing several solutions for PowerBi based on REST API's returning JSON.
I know that refresh datasets for rest apis isnt supported right now for PowerBi Service.
There are several workarounds right now like
and
But I didnt get it working.
let Source = Json.Document(Web.Contents("https://my.APIURL.com/", [RelativePath="api/entries?time_since=2017-11-01%2000:00:00&time_until=2017-11-30%2000:00:00" ,Query=[ time_since="2017-09-01 00:00:01", time_until="2017-12-31 00:00:01" ]] )), entries = Source[entries], #"ConvertToTable" = Table.FromList(entries, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expand" = Table.ExpandRecordColumn(#"ConvertToTable", "Column1", {"id", "users_id", "projects_id"}) in Expand
The problem is that the api returns an error, if it is called without time_since & time_until Parameters.
Somebody have some suggestions or recomandation to articles or post or can help me directly?
In PBID it is working perfectly.
I appreciate you effort.
Thanks.
best regards.
Wolf
Solved! Go to Solution.
@WolfBiber,
What error message do you get this time? In your scenario, write the code as follows, then sign in as anonymous in Power BI Service and check if the refresh works.
let Source = Json.Document(Web.Contents("https://my.APIURL.com/", [RelativePath="api/entries",Headers=[Authorization="api-key xxxxx"] ,Query=[ time_since="2017-09-01 00:00:01", time_until="2017-12-31 00:00:01" ]] )), #"ConvertToTable" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expand" = Table.ExpandRecordColumn(#"ConvertToTable", "Column1", {"id", "users_id", "projects_id"}) in Expand
Reference:
https://community.powerbi.com/t5/Service/Refreshing-a-Restful-Data-Source-with-API-Key/td-p/131298
Regards,
Lydia
Hey,
please excuse my late response.
Finally today I found time again to work in this project.
The authentication is Basic. So no prob with apikey.
My Problem was that the api at his entry point returned 404.
Excluding the api url and just putting
Source = Json.Document(Web.Contents("https://my.APIURL.com/",[RelativePath="api/entries"])
is working.
I had a Query with
Source = Json.Document(Web.Contents("https://my.APIURL.com/api/",[RelativePath="entries"])
which returned the error in PBIS.
Many thx for your support @v-yuezhe-msft
I need to pull data for a certain time range from elastic search to Power BI. I am using "Web" as data source. I am able to connect to elastic search in Power BI and view data using URL below however, I am not sure how to add time range to below URL. Field "InsightTimeX" has timestamp.
Please help.
Thanks,
Neeraj
@WolfBiber,
What error message do you get this time? In your scenario, write the code as follows, then sign in as anonymous in Power BI Service and check if the refresh works.
let Source = Json.Document(Web.Contents("https://my.APIURL.com/", [RelativePath="api/entries",Headers=[Authorization="api-key xxxxx"] ,Query=[ time_since="2017-09-01 00:00:01", time_until="2017-12-31 00:00:01" ]] )), #"ConvertToTable" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expand" = Table.ExpandRecordColumn(#"ConvertToTable", "Column1", {"id", "users_id", "projects_id"}) in Expand
Reference:
https://community.powerbi.com/t5/Service/Refreshing-a-Restful-Data-Source-with-API-Key/td-p/131298
Regards,
Lydia
Hey,
please excuse my late response.
Finally today I found time again to work in this project.
The authentication is Basic. So no prob with apikey.
My Problem was that the api at his entry point returned 404.
Excluding the api url and just putting
Source = Json.Document(Web.Contents("https://my.APIURL.com/",[RelativePath="api/entries"])
is working.
I had a Query with
Source = Json.Document(Web.Contents("https://my.APIURL.com/api/",[RelativePath="entries"])
which returned the error in PBIS.
Many thx for your support @v-yuezhe-msft
@WolfBiber,
What is the result when you open the URL in web browser? You can use Postman to test your API.
Regards,
Lydia
Check out the July 2025 Power BI update to learn about new features.