1

My problem is very easy: I'm retrieving all stocks about lithium on the ASX market from this web page.

I created a Power BI dashboard that you can visualize here.

As the source is Web I would like to refresh it every day using the Gateway connection, so I have successfully installed it on my computer that I turn on every day:

enter image description here

The problem is that instead of a successful daily refresh I have a daily error:

Failed to test the connection to your data source. Please retry your credentials.
Some credentials aren't validated as they are set to skip test connection.

enter image description here

If I enter in the Edit credentials I see the error: 
Failed to update data source credentials: The credentials provided for the Web source are invalid. (Source at https://www.marketindex.com.au/commodities/lithium.)Show details

This because I'm using Authentication method: Anonymous and Privacy level setting for this data source: Public. But this is exactly what I'm using on Power BI desktop and it's working!

enter image description here

What am I doing wrong here?

Why the same connection is working fine on Power BI Desktop but not the same dashboard uploaded online?

EDIT: the On-premises data gateway looks installed without problems

enter image description here

Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113

2 Answers2

1

It seems like the issues you're experiencing may be caused by the link you're using (https://www.marketindex.com.au/commodities/lithium), although I'm not sure exactly why.

For example, as has been mentioned in the Do I Need a Gateway for Web post, one can use Web.Contents instead of Web.BrowserContents in the raw M code in the Power Query Editor to avoid using a gateway. However, when I test this implementation using the link that you provide, I get an error when refreshing from Power BI online.

There was an error when processing the data in the dataset.

If I test this implementation using an HTML table from the https://tradingeconomics.com/commodity/lithium website, though, and set the data source credentials as follows, then I am able to successfully refresh the dataset from Power BI online.

Data Source Credentials

I tested this with another HTML table from a random webpage and an online refresh worked as well. So it seems like there is something odd about how Power BI online interacts with the Market Index website and this is causing your issue. Potentially because of some authentication configuration on the Market Index side of things? I'm not able to help with looking further into why this is occurring, though.

Would you be able to use a different website with similar information as a data source for your report? If so, you could avoid the issues you're experiencing and avoid using a gateway in general if you wanted.

If you go this route, it seems like using the included "Scheduled refresh" setup in Power BI online works, but I will include the Power Automate scheduled refresh setup below in case this is desired instead.

Power Automate Scheduled Refresh

One can use Power Automate to "schedule" dataset refreshes instead of the included "Scheduled refresh" functionality in Power BI online. The steps are a little too long to include here, but the Refresh your Power BI dataset using Microsoft Flow article outlines the process on setting this up. There are various triggers that you can use to trigger your flow. The linked article uses one that triggers based on a SharePoint action, but there is a Recurrence trigger that can be used to trigger your flow at regular intervals/times.

Recurring Power BI Refresh

Just set up your flow, make sure that it is "turned on", and your dataset will refresh at regular intervals from the cloud.

Matt Kocak
  • 736
  • 2
  • 6
  • 25
  • I still cannot believe that Power BI has no built in automation for this and we have to use Flow. Absurd! – Francesco Mantovani Jun 17 '22 at 12:16
  • I tested the flow, it's not working. The Flow is successful but the data are still those of yesterday. I did it multiple times – Francesco Mantovani Jun 22 '22 at 07:24
  • The problem is not at Flow level but at Power BI: even if I click refresh from Power BI the dataset is not updated: https://snipboard.io/sfD3ER.jpg – Francesco Mantovani Jun 22 '22 at 07:27
  • If I click onto the error in Power Bi it's still asking for the data gateway: https://snipboard.io/56Zd0o.jpg So the data gateway is still at the core of the refresh and even the manual refresh is not working. – Francesco Mantovani Jun 22 '22 at 07:29
  • It's odd that a gateway is required for this web connection. I will look into this. In the meantime, I found the following workaround to avoid a gateway https://community.powerbi.com/t5/Power-Query/WORKAROUND-How-to-use-scheduled-refresh-of-a-web-page-without/m-p/1434043#M44835 – Matt Kocak Jun 22 '22 at 16:00
  • 1
    After some more investigation, it seems like there was an update a few years ago where web connections do generally need a gateway. It seems like this could be avoided by using Web.Content instead of Web.BrowserContent in the M code. I will update the solution after I test this out. – Matt Kocak Jun 22 '22 at 19:04
  • Good investigation. However that solution was already suggested here: https://stackoverflow.com/questions/72235312/power-bi-do-i-need-a-gateway-connection-when-importing-data-from-a-web-page . I didn't checked if it worked because changing from `Web.BrowserContent` to `Web.Content` was breaking the table import. Let me know if this works for you, my previous dashboards was more complex so it might work. But damn Microsoft, this was supposed to be easier! – Francesco Mantovani Jun 22 '22 at 20:00
  • Today I also tested the use of `parameters` like in this example: https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure . I now can see the parameters on my Power BI online dataset but they look like a variable I have appended there and there are no instruction about how to use them. But apparently this is one of the things that will allow you to use the scheduled refresh that Power BI propose out-of-the-box. This is also the reason why a manual refresh doesn't work, because we need parameters.... Mind-blowing how difficult this can be... – Francesco Mantovani Jun 22 '22 at 20:05
  • 1
    I did some additional investigation and updated my solution to provide my results. It seems like it is a problem with the website that you're using as a data source. – Matt Kocak Jun 24 '22 at 17:56
  • Thank you for testing. I tested too using HTML as source but my Data source credentials are grayed out: https://snipboard.io/4cvEsd.jpg . – Francesco Mantovani Jun 25 '22 at 21:28
  • I also tested with the NBA ranging page https://www.nba.com/standings and it's the same. Do you have any page that I can use as test? – Francesco Mantovani Jun 25 '22 at 21:45
  • Hmm I haven’t experienced the grayed out Data source credentials that you are describing. It worked fine for me with the Trading Economics link that I mention in the solution. But it seems like some other users have experienced this issue. Maybe specifically turning your gateway off would resolve the issue? https://community.powerbi.com/t5/Service/Data-Source-Credentials-Greyed-out/m-p/519216 – Matt Kocak Jun 25 '22 at 23:09
  • Naah, there is no way to turn it ON/OFF https://snipboard.io/qsoi7b.jpg . Probably the greyed filed will be available once I install the gateway on an always on server somewhere. Anyway, thank you @Matt for your help. The bottom line is that setting up an easy refresh is not easy at all and the gateway path is probably the only one. – Francesco Mantovani Jun 26 '22 at 20:33
0

May this helps - Go to Options and Settings >> Data source settings >> edit permissions >> organization level. Save your changes.

Then go to powerbi.com, and update the data source credentials.

GowMaha
  • 1
  • 1