Can't Refresh without Gateway with Web API Source (new issue)

[BartdT]

Hi all,

having read (nearly) all posts and solutions for similar issues, I cannot figure out why this isn't working. I have a published PowerBI report which is refreshing automatically without the use of a gateway. Now I have added an additional Web API source and after publishing, a gateway is required all of a sudden to realize the automated daily refresh. For me it is not clear why this is, on the desktop version it refreshes without any problem and I'm running other published Web API's without a problem. This one should be an open and available feed and I've embedded the 'Get Contents' construction to prevent issues around that. 

To give the details, it is around this data source call (M-code), holding the API-url which also works directly in a browser: 

= Json.Document(Web.Contents("http://161.35.43.125:8080/metrics/instagram/dates/2020-08-01/2020-08-02")) 

Anyone able to replicate the issue and/or identify the cause? Would greatly be appreciated!

Krgds,

Bart

PS: I'll copy in some members who gave meaningfull comments on this topic before:

@GilbertQ @Fowmy 

[BartdT · Accepted solution]

Hi all, I received word from Microsoft (Power BI) Support with more information. I thought it was valuable to share it with you::

We have got an update from our Product Engineering team:

As we see that the User is using https and http urls to build the report. We checked with our SME they confirmed that, Power BI Service requires https urls for Web data sources to refresh. For non-secured urls (http), it's required to go through the gateway. By design.

So an anonymous API feed using "http" will not allow for a scheduled refresh in the online Power BI service without a gateway. It needs to be an https one.

Krgds,

Bart

[adnanarain]

I am using simple static web address with https but still i cant refresh my data without gateway. Can you please help me in this i am using below M code:

let
Source = Web.BrowserContents("https://gist.github.com/tadast/8827699"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.js-csv-data.csv-data.js-file-line-container > * > TR > :nth-child(1)"}, {"Column2", "TABLE.js-csv-data.csv-data.js-file-line-container > * > TR > :nth-child(2)"}, {"Column3", "TABLE.js-csv-data.csv-data.js-file-line-container > * > TR > :nth-child(3)"}, {"Column4", "TABLE.js-csv-data.csv-data.js-file-line-container > * > TR > :nth-child(4)"}, {"Column5", "TABLE.js-csv-data.csv-data.js-file-line-container > * > TR > :nth-child(5)"}, {"Column6", "TABLE.js-csv-data.csv-data.js-file-line-container > * > TR > :nth-child(6)"}, {"Column7", "TABLE.js-csv-data.csv-data.js-file-line-container > * > TR > :nth-child(7)"}}, [RowSelector="TABLE.js-csv-data.csv-data.js-file-line-container > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Country", type text}, {"Alpha-2 code", type text}, {"Alpha-3 code", type text}, {"Numeric code", Int64.Type}, {"Latitude (average)", type number}, {"Longitude (average)", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{""})
in
#"Removed Columns"

[Anonymous]

Hey,

try using this Code instead:

Source = Web.Contents("https://gist.github.com/tadast/8827699"),

[BartdT]

Hi all, after updating the API making it an anonymous https-call iso an http-call the online automatic refresh is allowed again. So this solves the issue.

Krgds,

Bart

[GilbertQ]

Hi there

When I click on the link it does not work.

What happens when you do this in PBI Desktop? Does it work.

[BartdT]

Thanks for the check. Yes, in Desktop ik works perfectly. I see that the link also picks up the last two brackets and quotes of the M-code, which do not belong to the URL. Please use this 'pure' link for the check (M code as a whole works incl the brackets/quotes):

http://161.35.43.125:8080/metrics/instagram/dates/2020-08-01/2020-08-02

Krgds,

Bart

[BartdT]

Any luck? @Gilbert 

Anyone knows how I can address this to Microsoft support?

[GilbertQ]

Hi there

have you configured the data source credentials for the dataset?

Even though it is a web data source you still have to configure it with Anonymous access.

[BartdT]

@GilbertQ 

Hi, which configuration (and where) are you referring to? I'll share some info of the existing configuration in two places:

1. On Desktop the source credentials are set to Anonymous and Privacy Level to None. Note that this is similar to another Web API I have and which does work and allows for automatic refresh in the Power BI Service (without the Gateway)

2. As indicated, in Power BI service the scheduled automatic refresh is not allowed if this new Web API is included, at least not without the gateway. To do some additional checks, I've also used my personal gateway and added the data source to the gateway. Data Connection tests are succesful for all sources, also the new one, and I can schedule and refresh using the Gateway. After this step, I am not allowed to switch the Gateway off and set up the automatic refresh without the Gateway. So there's still some issue but not sure how to get more info on this.

I wanted to share some screen shots of all this, but not sure how I can add these, copy-paste is not allowed and I am not sure how the insert-picture feature works (Source  is an open text field). Also attachment cannot be uploaded?

Hope this reply give some more insight, thanks again for helping out!

Bart

[GilbertQ]

Hi there

It would appear that your data source has to be used with a gateway based on your findings above.

[BartdT]

Hi, ok. Is there any way to find out why this is? Still wondering what makes this Web API different from my other ones and if there's any way to make it work. Anyone?