Since the Olympics is in full swing, we want to keep up to date on the latest medal counts and information, automatically.
In order to do this, we should be able to schedule a refresh on our report since it uses public web data as the source.
Schedule Refresh in PowerBI.com
There are a few things we need to overcome in order to succeed with the scheduled refresh. You may encounter two error messages commonly when trying to refresh your data in PowerBI.com. These messages relate to:
- Dynamic Data Source
- Gateway
Dynamic Data Source
In order to schedule the refresh on this dataset in PowerBI.com, we need to tweak the source code slightly. If you have a look in Power Query at the SampleResultsTable query we authored using the custom parameter you will see that Power Query generated the following code for Source step:
= Web.BrowserContents("https://olympics.com/en/olympic-games/" & URLpart)
This code is what is know as a 'Dynamic Data Source', which cannot be refreshed in PowerBI.com
A dynamic data source in Power BI is one which cannot be determined without running the Power Query code. Since this URL requires Power Query to run another query, it is a dynamic data source. In order to fix it, we need to provide a static base URL which can be accessed independently, and then use the RelativePath property to direct Power BI to each page based on our parameter.
We will update the source step to read:
= Web.Contents("https://olympics.com/en/olympic-games/", [RelativePath = URLpart])
Data Gateway
If we try publishing this to PowerBI.com, it now asks us for a Gateway connection.
A data Gateway allows you to manage access to secure data sources that aren't available otherwise. If you are using Excel files stored on SharePoint or OneDrive and get a Gateway error message on refresh, this post is for you too!
Why the error?
If our data is available on the web with no login required, or in the cloud in SharePoint with the same login credentials we're using for PowerBI.com, why does PowerBI.com insist on a gateway?
Data Gateway & Web.BrowserContents
Web.BrowserContents as a function requires the secure gateway connection to our datset. In order to refresh the dataset we have two options:
- add the Web.BrowserContents data sources to a gateway
- convert all queries to use Web.Contents instead
Data Gateway and OneDrive / SharePoint files
If you connected to a SharePoint or OneDrive Excel file using the Get Data > Excel option, Power BI will use the local path specific to the current computer you're using. In order to access this same filepath, you'll need to build a gateway. Again, you have two options here:
- add the Excel.Workbook data sources to a gateway
- convert all queries to use Web.Contents instead
Add data to Gateway
If you are lucky enough to have admin access to your company's gateway, this is a simple process that you can do from PowerBI.com. If you don't yet have admin access to a gateway, install one!
Your gateway must be installed on a computer that is always running. If the computer stops running or goes to sleep, so does your gateway, and so too will your scheduled refreshes.
Once you've installed your gateway, using it is straightforward. Simply add each data source to the gateway using the blue links seen in the screenshot above.
Convert Source: No Gateway Needed
Alternatively, we can convert the source steps to use Web.Contents, and avoid the need for the Gateway. Do I hear a sigh of relief here?
Web Data
In our Olympics example, this process is easy - simply edit the formula bar for the Source step wherever you see Web.BrowserContents and change to Web.Contents.
NOTE: If you have already published to PowerBI.com, you will need to delete the data source before trying to republish. If you simply overwrite the data source your changes will not pull through.
One Drive / SharePoint Data
For a OneDrive / SharePoint file, I recommend that you use the Get Data > Web option, then paste the Filepath of your desired document. This filepath can be found in SharePoint by clicking the three dots next to the file you want, then select Details (you may need to click More, then Details). Scroll down on the Details pane and click the Icon to copy the filepath.
Once you've loaded this data into Power BI, you can see what the Source step needs changed to in order to avoid the need for a gateway.
Update Credentials
Even though we don't need a Gateway for this method, we still need to update our credentials in PowerBI.com in order to schedule the refresh. When you're in the dataset settings page on PowerBI.com you'll see a prompt to 'Edit Credentials'. You must do this for each data source in your file. Once you've done this once, it will allow you to enable scheduled refresh on the dataset.
In order to Update credentials for Web or SharePoint based data sources;
Step 0: If you're getting data from SharePoint, ensure you've used the correct link. See more info in my Get Data From SharePoint blog.
Step 1: Navigate to your workspace in PowerBI.com
Step 2: Click the 'Schedule Refresh' icon next to the dataset you wish to refresh.
Step 3: Scroll to the 'Data Source Credentials' area. Click 'Edit credentials' for each data source and sign in with Oauth (or the correct credentials for your chosen data source).
