Search

Search this blog:

Search This Blog

Refreshing COVID-19 NZ Data: RelativePath and Query options with the Web.Contents M function


The first of the Paralympic Games medals have been earned already - I'll be posting a blog soon on how to use .pbit files and update data sources using parameters. 

COVID 19: New Zealand is now in Level 4

Today though I'm going to digress slightly from the celebratory mood of the Olympics and return to the COVID 19 data analysis. I've had a few requests from friends and family to dig up my old report and visualize what's going on in the NZ community (not including MIQ facilities). View the full COVID 19 NZ report in Power BI.

screenshot Power BI report page

Question: What do the NZ Community COVID cases look?

The Ministry of Health website graphs all NZ cases, including those in Managed Isolation and Quarantine. It's difficult to get a good picture of how many community outbreak cases we're dealing with, and how that compares to the other outbreaks we've seen in New Zealand. 

So what does the COVID 19 data look like over time if you remove the Managed Isolation & Quarantine cases?

Power BI column chart: New Cases by Date of Report

Unfortunately, the Ministry of Health website has changed quite a bit since I first created the COVID report, and there are no longer any web based tables available to use as data source.

Challenge: Auto Refresh a Dynamic Data Source

Instead, all the data I need is uploaded daily in a CSV file which includes the name of the date in the filename. The web link to access this CSV file is: 

https://www.health.govt.nz/system/files/documents/pages/covid_cases_2021-08-25.csv

In order to make the refresh dynamic, I'm going to use a few Power Query tricks. 

Step 1: Identify Data Source parts

The CSV file seems to be uploaded between 1-4pm each day for that day, so if I schedule the refresh for 4pm daily I can use today's date as part of the data source URL.

Recall the URL is: 

https://www.health.govt.nz/system/files/documents/pages/covid_cases_2021-08-25.csv

We're going to break this into:

  • Root website: https://www.health.govt.nz
  • Relative path part 1: /system/files/documents/pages/covid_cases_
  • Relative path part 2: 2021-08-25
  • Relative path part 3: .csv

Note the Relative path part 2 matches today's date in the format: yyyy-MM-dd

Step 2: Generate Dynamic Relative path part

I can't use a normal parameter for the Relative path part 2 because I need it to update automatically on refresh. So, I'm going to create a new blank query instead. This Query is really simple, just has one step that combines a few M code date functions: 

let
    Source = Date.ToText(Date.From(DateTime.FixedLocalNow()), "yyyy-MM-dd")
in
    Source

I have named this query: DateCSV

Step 3: Use relative path to get Dynamic Data

Normally we'd be able to split the URL into parts, but with a dynamic data source that will give an error.

INCORRECT Code

let
    Source = Web.Contents("https://www.health.govt.nz/system/files/documents/pages/covid_cases_"&DateCSV&".csv")
in
    Source

Unfortunately, if you use the above Source code and Publish to PowerBI.com, you'll get the following error explaining that dynamic data sources cannot be refreshed in the Power BI service: 

screenshot Power BI refresh error

This error is not very helpful - it doesn't even specify the correct name of the query, instead just referring to 'Query1'.

When you follow the link to Learn more from the error message, you will find a short message that doesn't quite provide you with enough information to resolve the data error: 

In most cases, Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

Picking up on the keywords, a quick web search for "RelativePath and Query options with the Web.Contents M function" returns Chris Webb's super helpful blog.CrossJoin post Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code, to which I have referred many times.

Finally, we're getting somewhere and you may finally be able to see why I split the URL into a Root path and four Relative paths in Step 1.

CORRECT Code

let
    Source = Web.Contents("https://www.health.govt.nz", [RelativePath = "/system/files/documents/pages/covid_cases_"&DateCSV&".csv"])
in
    Source

This correct code uses a static URL for the Web.Contents() function. You'll find this process easier if the root URL is an actual functioning website, which is why my Relative Path part 1 is still static.

Now we can refer to the dynamic DateCSV query within the RelativePath portion of the Web.Contents() function and overcome the dynamic data refresh limitations in Power BI service.

Voila! Now we can schedule a refresh on this dataset in Power BI service.

Custom Visual Review: Charticulator

This is not your ordinary custom visual - this is EVERY custom visual. Charticulator puts the power to design and develop custom visuals to ...