cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WolfBiber
Microsoft
Microsoft

Dataset Refresh REST API & JSON with required Param in PB service

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

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

and

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

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

2 ACCEPTED SOLUTIONS
v-yuezhe-msft
Microsoft
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

 

View solution in original post

5 REPLIES 5
neeraj0001
New Member

@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. 

 

http://10.65.44.200:9200/neo4j-index-node/_search?q=_type:%22JENKINS%22%20AND%20dataType:%22stageInd...

 

Please help.

 

Thanks,

Neeraj

v-yuezhe-msft
Microsoft
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

 

View solution in original post

@WolfBiber,

What is the result when you open the URL in web browser? You can use Postman to test your API.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

thx a lot.

@v-yuezhe-msft its solved. It just was a mistake from me

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors