How to avoid having to use a personal gateway for web data sources (html page or table) in Power BI
When you import publicly available data into your report you will most likely use the web connector in Power Query.
This might lead to issues later on when trying to refresh your dataset.
Here is why:
When you select the Web connector in Power BI (or Power Query) to load publicly accessible data (with anonymous authentication)
This function will be automatically created:
This will lead to issues when you publish your report to the Power BI Service and try to refresh it.
As you can see, the gateway connection option is on and data source credentials is greyed out in the Power BI service.
This means you can only refresh your data after installing a personal gateway on your machine and we do not want that.
So what can be done?
Well it is pretty easy, manually change the function to:
Now, when you publish your report you will see the following:
Power BI tells us this is a public data source and it does not need a personal gateway.
Now we can also set our data source credentials with the correct authentication method and privacy level.
Replace the default function created by the web connector Web.BrowserContents with Web.Contents as outlined above.
Microsoft's documentation states that:
Web.BrowserContents "Returns the HTML for the specified url, as viewed by a web browser."
Web.Contents "Returns the contents downloaded from url as binary"
If you have had trouble with this feel free to comment and share your thoughts.