Data refresh in PowerBI Service

[Arendp]

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.

[nickyvv]

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

[Arendp]

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. 

[Arendp]

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

[Arendp]

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]

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