Data refresh from web source requires Personal Gateway?

[helenah]

Hi,

Lately I have had problems connecting to web data sources with Power BI. The connection works fine in Power BI Desktop but I am not able to create a scheduled refresh for the data on Power BI site. The site requires me to set up a Personal Gateway which, based on my knowledge, should not be needed in case of web as data source.

I managed to solve one data source (which was Google Sheet) by changing the type to xls instead of html.

However, this does not seem to solve my problem for some other (non Google sites).

Since these same sources have been in use since June, I believe there is something that was changed in the latest update which now creates this problem.

Example of not ok data source: https://docs.google.com/spreadsheets/d/1MnrDYPNhXOucVRe9tMF_x1WshYP46q1o5Oq2TnHnJiA/pub?single=true&...

However, if I change the end to &output=xls then it works OK. A tip for this I got from this community, thanks!

Another source which used to work OK is:

http://sdw.ecb.europa.eu/browseTable.do?dc=&removeItem=&ec=&rc=&legendPub=published&oc=&node=9691297...

This does not work currently (works in Desktop but I am not able to refresh from Power BI site where e.g. scheduled refresh is set up. I tried another link for same souce but linking to XLS instead:

http://sdw.ecb.europa.eu/export.do?dc=&removeItem=&rc=&ec=&legendPub=published&node=9691297&oc=&df=t...

But this does not seem to work either 😞

br,

Helena

[v-ljerr-msft]

Hi @helenah,

I have tested it on my side using the last URL provided above as web source to create a report, then published it to the service, and I can do a schedule refresh and manual refresh on that dataset without any issue.

URL I used for test: http://sdw.ecb.europa.eu/export.do?dc=&removeItem=&rc=&ec=&legendPub=published&node=9691297&oc=&df=t...

Following is query I used in Advanced Editor.

let
    Source = Csv.Document(Web.Contents("http://sdw.ecb.europa.eu/export.do?dc=&removeItem=&rc=&ec=&legendPub=published&node=9691297&oc=&df=true&FREQ=M&pb=&legendNor=&SERIES_KEY=120.EXR.M.USD.EUR.SP00.A&SERIES_KEY=120.EXR.M.GBP.EUR.SP00.A&SERIES_KEY=120.EXR.M.DKK.EUR.SP00.A&SERIES_KEY=120.EXR.M.PLN.EUR.SP00.A&SERIES_KEY=120.EXR.M.SEK.EUR.SP00.A&SERIES_KEY=120.EXR.M.NOK.EUR.SP00.A&SERIES_KEY=120.EXR.M.RUB.EUR.SP00.A&legendRef=reference&DATASET=0&exportType=xls"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",4),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows")
in
    #"Promoted Headers"

Have you received any error when you tried to do the refresh on the service? Could you try deleting the whole dataset and republish it to the service to see if the issue remains?

In addition, please go to check if the data souce credentials are still valid for that dataset.

Regards

[helenah]

Hi,

If I do a completely new report (and dataset), then this seems to work OK. Just editing the existing report does not solve the issue. This pretty much means that I will need to recreate the report with several web data sources from scratch again? And hope that this time it will work (until some changes are again made that causes it to fail...).

@v-ljerr-msft

Helena