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
Arendp
Helper III
Helper III

Data refresh in PowerBI Service

Not sure if this was already discussed before, I looked several times for a solution but I can't get a solution. It looks a lot like the problem described here: https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/

But I can't get it resolved.

 

I have a connection with an API. And I have therefore the following M code

 

let
    Source = 
        List.Generate(
            ()=>[Counter=0],
            each [Counter]<10000,
            each [Counter=[Counter]+100],
            each Json.Document(Web.Contents("https://company.simplicate.nl/api/v2/hours/hours?offset="& Text.From([Counter]) & "&limit=100", [Headers=[#"Authentication-Key"="xxxkey", #"Authentication-Secret"="xxxsecret"]]))
        ),
// more code
in
    #"Type gewijzigd2"

I use the counter variable because in this API I can only get 100 rows a time per API call.

 

With PowerBI Desktop this is working fine, but with PowerBI Service I get this message while trying to refresh:

Refresh for this dataset is not possible because the following datasources are not supported for this.

And then the datasources that work with variables (just like mentioned in above code) are shown.

 

Does anyone know how to solve this?

Let me know if you need more info.

5 REPLIES 5
nickyvv
Community Champion
Community Champion

I believe you were in the right direction by looking at the blog of Chris Webb.

I found a solution to the Web.Contents method error here:

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

In short: you have to build a valid URL inside your Web.Contents method and then add the query parameters to it later, so that Power BI can do a static analysis on the URL.

 

I hope this helps.

 

Did I answer your question? Then please mark my post as a solution!
My blog: nickyvv.com



Did I answer your question? Mark my post as a solution!

Blog: nickyvv.com | @NickyvV


Thanks for your reply.

I am trying to work this out but I am stuck.

 

this is my code, can you see where it's going wrong?

each Json.Document(Web.Contents("https://testadmin.simplicate.nl/api/v2/hours/hours", [Query=[offset=Text.From([Counter]),limit=100], [Headers=[#"Authentication-Key"="auth-key-code", #"Authentication-Secret"="auth-secret-code"]]))

 

I get this error: Expression.SyntaxError: Invalid id.

when I try to use a number (for example 100) instead of Text.From([Counter]) it also doesn't work.

[Counter] contains a counter that gives back 100 rows each time as long as there are rows. This is to be found in my first post. 

 

I think the problem is, how to combine Query and Headers in 1 part of the web.contents function.

Found it out already, it's all about putting the comma's and brackets at the right place 🙂

And numbers need to be converted to text.

This is my result:

 

each Json.Document(Web.Contents("https://testdemo.simplicate.nl/api/v2/hours/hours", 
                        [
                                Query=[offset=Text.From([Counter]),limit=Text.From(100)], 
                                Headers=[#"Authentication-Key"="auth-dey-code", #"Authentication-Secret"="auth-secret-code"]
                        ]
                                ))
blopez11
Resident Rockstar
Resident Rockstar

The power bi service doesn't like the first parameter to Web.Contents to be dynamic.  The first parameter should be the base of the url, then you use the Web.Contents RelativePath and Query options to add the remainder and parameters respectively

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