Excel examples for your work, sports and more.: May 2021

#75: Power BI dashboard "COVID-19 in the Netherlands" with CoronaWatchNL-data

This post is about my third (and last) dashboard "COVID-19 in the Netherlands", with data from CoronaWatchNL. I made this dashboard with Microsoft Power BI. The 2 previous dashboards in this series, which I made with Tableau and Google Data Studio, you can find here and here. In March 2020, I also made a Power BI COVID-dashboard (see here), but the data in that dashboard was not refreshed automatically, while this happens in this new (2021) Power BI dashboard. And the new Power BI report has some other improvements as:
- it has a has a responsive design, so it fits well on each type of device (see FIG.1)
- it has a (new) (shape-)map
- it has a improved dataset, which made it possible to combine the 3 COVID-19 metrics (# Diagnosed, Hospitalized and Deaths) in one chart

FIG.1: My COVID-19 dashboard in Power BI, on tablet, laptop and mobile

Shape Map

The report has the Shape Map custom-visualization Drilldown Choropleth (which you can download here), with 2 layers: provinces and municipalities in the Netherlands, which data is in these 2 TopoJson files:

https://raw.githubusercontent.com/cartomap/nl/gh-pages/wgs84/provincie_2020.topojson

https://raw.githubusercontent.com/cartomap/nl/gh-pages/wgs84/gemeente_2020.topojson

And I saw on the website of these files, so:

https://github.com/cartomap/nl

https://cartomap.github.io/nl/

that they also have other TopoJson-files, e.g. for GGD (Dutch Municipal Health Services), which could be interesting if you would like to make a GGD-map, so one that shows the COVID-19 numbers in the 25 GGD-regions.

FIG.2: Shape Map, showing layer-1 of map: Provinces

FIG.3: Shape Map, showing layer-2 of map: Municipalities

NB: The (score-)Card 'Total Diagnosed' shows a 'normal' number, so not abbreviated as 331K, and how to do this, I found on:
https://community.powerbi.com/t5/Desktop/Number-Formatting-on-Card-Visualization/m-p/229483

In the beginning of the COVID-19 outbreak in the Netherlands, in March 2020, I made a similar Shape-map for my work, with Microsoft Reportbuilder. This is a free 'classical' reporting- tool (download-link) to make paginated reports that can be printed, while more modern reporting-tools as Power BI are meant for visual exploration ('slicing-and-dicing') of the data.

My COVID-19 Report Builder report (see FIG.4) shows 3 kinds of information:

*A: the Dutch municipalities where your employees live, ranging from 0 (white) to 100 (dark grey)

*B: # COVID-19 cases, the green/yellow/red circles, ranging from 0 (green) to 100 (red)

*C: # employees that live in a municipalities with COVID-19 cases, ranging from 0 (small circle) to 100 (big circle)

The goal of this report was to see if a company has employees which live in municipalities with a high number of COVID-19 cases and if so, propose them to work from home. FIG.4 shows the situation (for a fictitious (test-)company) in March 2020, when the (known) COVID-19 cases were concentrated insmall amount of municipalities. The first weeks, we updated this COVID-19-report frequently, to adapt the max. values for # COVID-19 cases (B in Legend of map), but as soon COVID-19 was spread in all the Netherlands, we stopped with it, and the report was not needed anymore as everyone started working from home (and more than 1 year later, most of us still are...).

FIG.4: Microsoft Report Builder report that combines COVID-19 data (in beginning of outbreak) and employee-location data.

Update 31/5/2021: for a new version of this report, now made in Power BI with a ArcGIS-map, see the next post.

Dataset

The 3 COVID-19 metrics that I use in my report (# Diagnosed, Hospitalized, Deceased) are stored in the CoronaWatch-files in such a way that it would not be possible to draw more than 1 metric in a chart (each metric is in a separate row, instead that they are all in 1 row). Power BI has the Power Query language 'M' to do all kind of data-transformations, and in this case I used the Pivot-function to get all the 3 metrics from 3 rows in 1 row with 3 'metric-columns' (see FIG.5-7).
NB: For more info about this function, see e.g.:
https://radacad.com/pivot-and-unpivot-with-power-bi

The CoronaWatch-files has both the metric day-values ('delta') as the total (cumulative) values, and to be able to do the pivot, I had to split these 2 metric-values in 2 separate datasets, e.g.: 'RIVM_NL_municipal_latest_NewCases' and 'RIVM_NL_municipal_latest_TotalCases', and then join them together (resulting in dataset 'RIVM_NL_municipal_latest'), what can be done in M using the Expand function (see FIG.5-7). To be able do this join, I created a datamodel in which I linked the 3 datasets (1:1 relations) (see FIG.8).
NB: For more info about this function, see e.g.:
https://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query

For dataset 'RIVM_NL_municipal_latest' , I used 2 other M-functions:

- Sort, so that table is sorted by date, province and municipaltiy.
NB: For more info about this function, see e.g.:
https://support.microsoft.com/en-us/office/sort-a-table-power-query-baf200d5-1097-49cf-80b3-f1ce07b5f114

-Replace, to get rid of NULL values.
NB: For more info about this function, see e.g.:
https://www.youtube.com/watch?v=EoRjNx1G2g4

FIG.5: datasource of report

FIG.6: datasource of report and transformation of data with M (Power Query)

FIG.7a: datasource of report and transformation of data with M (Power Query)

FIG.7b: datasource of report and transformation of data with M (Power Query)

FIG.8: tables of datasourcein Power BI Datamodel

In the upper chart in tab-2 of the report you can an example of a chart that has now more than 1 metric (e.g. Hospitalized and Deceased).

Mobile report

I made a mobile layout for the report, so it automatically displayed in the best format for the device (PC, mobile or tablet) on which the report runs (as you can see in FIG.1).

For a nice video that explains all the features of the Power BI Mobile app (so Power BI for mobile devices as smartphones and tablets), e.g. natural language query and query by voice-message, see:
https://youtu.be/pF_ST8B_XU0

FIG.9: mobile report design in Power BI Desktop

Power BI Service (cloud) features: Dashboards,
Machine Learning generated reports, natural language query, sharing and colaboration

After publishing a Power BI report to the cloud (Power BI Service), I normally do a quick check with the 'Quick Insights' feature to see if the build-in AI (Machine Learning) detects some interesting patterns which I didn't see, so also this time. It detected e.g. there is a correlation between Diagnosed and Hospitalized, or that in April 2020, there the Hospitalized-number had some outliers.

FIG.10: Quick insights on datasets

FIG.11: Quick insights Reports

FIG.12: Quick insights Report: correlation between Diagnosed and Hospitalized numbers

For more details about Quick Insights / AI in Power BI, see:
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-insight-types

In the Power BI Service, it is possible to pin elements of a report to a dashboard, to show for example just some high-level numbers that fit on 1 screen, which can be usefull for a CEO, who might not be interested in more detailed break-down charts with which you can interact, which might be more a task for a business analyst. I made a dashboard (for PC and mobile) that shows the 3 main COVID-19 metrics (total Diagnosed, Hospitalized, Deceased), and by clicking on them, you go to the corresponding report of these charts. And the dashsboard has also 3 charts coming from 'Quick Insights' (so charts which were not in a report I made).

FIG.13: Power BI dashboard (desktop version)

FIG.14: Power BI dashboard (mobile version)

Power BI also has colaboration feaureIt is possible to comment on / annotate dashboards and reports that are in the Power BI Service. Here you can see some examples of this (from mobile):

FIG.15: comment in (mobile) report

FIG.16: anotate in (mobile) report

In Power BI Service, it is now also possible to create reports (before, it was only posssible todo this with Power BI Desktop). So if you share your workspace with the datasets that you created (in Power BI Desktop) with someone else, he can create his own report for this, as you can see here:

FIG.17:Power BI report made in the cloud (Power BI Service)

In Power BI Service, you can set the schedule for the refresh of the data of your datasource, see. e.g.: https://docs.microsoft.com/en-us/power-bi/report-server/configure-scheduled-refresh
For My COVID-19 report I set the refresh-schedule to Daily, as this was the frequency with which my datasource (CoronaWatchNL ) was updated, but... :

End of CoronaWatchNL and consequence for my dashboard

CoronaWatchNL stopped at 17/4/2021, so my Power BI dashboard (and also the 2 other ones in Google Data Studio and Tableau) are not COVID-19 live-trackers anymore. CoronaWatchNL recommends to use the data of the RIVM-website, which is now of good quality, making CoronaWatchNL's work not necessary anymore as they say.

Although CoronaWatchNL is history now (and hopefull COVID-19 will soon be as wel..), we should remember that in the period of over 1 year of CoronaWatchNL's existence, this was the best (Dutch) COVID-19 datasource for reporting, especially in the beginning of 2020, when using the RIVM-data for reporting was not really an option. For the details why, read this article of Follow the Money, for which CoronaWatchNL-founder Jonathan de Bruijn was also interviewed, and made this nice statement about the RIVM initial problems with sharing COVID-19-data:

We hebben [aan RIVM] toen aangeboden om te helpen die [COVID-19 data op RIVM-website) te structureren, maar daar hebben ze geen gebruik van gemaakt.’ Met een lach voegt hij toe: ‘Waarschijnlijk hadden ze het te druk om te zien dat wij ze hadden kunnen ontlasten.’

So CoronaWatchNL, Jonathan and Roos and all other people who helped in this Open Data project, thanks for all the effort!

I'm not sure yet if I will still do this datasource-replacement (so CoronaWatchNL -> RIVM) for my COVID-19 in NL dashboards, but I've seen a Tableau Public dashboard that did do this, see:
https://public.tableau.com/profile/jolanda.luime#!/vizhome/CoronaMonitorNoord-Brabant/OntwikkelingNederland

which has this datasource-link:
https://www.rivm.nl/en/novel-coronavirus-covid-19/current-information

Microsoft Power BI Desktop vs Tableau Public vs Google Data Studio

So I used for my COVID-19 Dashboard these 3 free tools to create them:

- Microsoft Power BI Desktop

- Google Data Studio

- Tableau Public

Which one would I recommend to someone who is looking for a free BI-tool?
Tableau Public has the big limitation that you can only store your work on their website: https://public.tableau.com. For me, this was not a problem, as my report was for a (public) COVID-19 dataset (CoronaWatchNL), but I guess for most reports you don't want to make it public, but just share it with your co-workers. Another big disadvange of Tableau Public is that the number of possible datasources you can connect to is limited.

So that leaves Microsoft Power BI Desktop and Google Data Studio. If you don't have a paid Power BI license, and so you don't have an account for Power BI Service (cloud), your sharing-options are more limited. You can share your report by email or embedding your report in your website, as I did here (see the end of this blog post). With Google Data Studio, sharing is just as easy as for any other Google Drive file (e.g. a Google Sheets doc). And if your dataset is already in a good shape for visualization (so no data-transformations needed), I think Google Data Studio is the best option. But if you have to prepare your data before you can make your dataviz (as I had to pivot the data of CoronaWatchNL) or want more advanced datav-options (as the Shape Map for Dutch municipalities that I used), then I would recommend Power BI.

I also googled a bit to see what others say in a comparision of these BI tools, and I found in a post about Google Data Studio here this statement:

Not everyone needs an airplane to get to work in the morning

And this post, from 2016, when Google Data Studio was just made general available, also says:

The market is now starting to make visualization solutions accessible to small organizations without the means to pay a team of elite analysts and developers to take care of visualizing their data.

So what they mean is that Google Data Studio (100% free) would be a better option for a lot of (smaller) companies than Power BI or Tableau (which are not free if you want to use all it's features). For a more recent (2021) review of GDS, see e.g.:
https://towardsdatascience.com/google-data-studio-for-beginners-99355e9df809

If the choice for a BI-tool wouldn't be constrained by budget, then at this moment (2021), according to the Magic Quadrant for Analytics and BI tools of analyst house Gartner, Power BI would be the best choice, see e.g. :

https://powerbi.microsoft.com/en-us/blog/microsoft-named-a-leader-in-2021-gartner-magic-quadrant-for-analytics-and-bi-platforms/

AI - COVID-19 hackathon University of Groningen

University of Groningen (RUG) will organize a AI - COVID-19 hackathon #aiHackCovid, and they asked me if I could maybe tell something about my COVID-19 dashboards. If you want to be a speaker, participant of the hackathon, or a member of the jury, let them know.

https://aihackcovid.aimedgroningen.com/

Update 27/6/2021 (after my presentation):

Here a post on Linkedin, which includes the powerpoint I used for the presentatio

And now for something completely different:

The Johan Cruyff Foundation, who work to make sport accesible for all children, including e.g. disabled children, organized a 14K virtual run to raise money, and the runners can help with that by asking people to sponsor them. I participated on 25/4/2021 (although the race is open until 19/5), see my Strava-activity, and raised 102 euro so far. If you also want to be my sponsor and donate something, you can do so here:

https://acties14k.cruyff-foundation.org/actie/maarten-van-reek?utm_source=kentaa&utm_medium=email-share&utm_campaign=cruyff-legacy-14k&locale=en

Here a video of the good work the foundation did on Greek islands with refugee-camps:
https://www.cruyff-foundation.org/en/news/first-cruyff-court-in-refugee-camp/

https://pt-br.facebook.com/cruyfffoundation/videos/192881359314861/?__so__=permalink&__rv__=related_videos

References

*Power BI:

https://www.datacamp.com/community/tutorials/data-visualisation-powerbi

https://blogs.msn.com/tools-to-sharpen-your-data-journalism-skills/

https://news.microsoft.com/microsoft365forjournalists/

https://news.microsoft.com/microsoft365forjournalists/learning-tools/visualizing-data-with-power-bi/

https://news.microsoft.com/wp-content/uploads/prod/sites/557/2020/11/AnatomyPoster-12.01.20.pdf

https://docs.microsoft.com/en-us/power-bi/consumer/end-user-insight-types

https://carldesouza.com/using-quick-insights-in-power-bi/