Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1378700%22%20slang%3D%22en-US%22%3EExcel%20for%20good%3A%20Building%20a%20dashboard%20to%20help%20visualize%20Covid-19%20infection%20in%20New%20Zealand%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1378700%22%20slang%3D%22en-US%22%3E%3CP%3EOver%20the%20past%20few%20weeks%2C%20I%E2%80%99ve%20been%20building%20and%20updating%20a%20dashboard%20that%20shows%20the%20development%20of%20the%20Covid-19%20infection%20in%20New%20Zealand.%20With%20the%20ever-changing%20data%20sources%2C%20I%20ran%20into%20quite%20a%20few%20challenges%20and%20had%20to%20come%20up%20with%20some%20workarounds.%20I%20want%20to%20share%20with%20you%20some%20of%20my%20approaches%20and%20decisions.%20This%20article%20gives%20just%20a%20short%20overview.%20Watch%20the%20video%20below%20if%20you%20want%20to%20go%20into%20detail.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMy%20main%20data%20source%20is%20the%20New%20Zealand%20Ministry%20of%20Health%20web%20site.%20The%20format%20and%20presentation%20of%20the%20data%20on%20that%20site%20changed%20a%20lot%20and%20I%20needed%20to%20update%20my%20queries%20almost%20daily.%20I%20also%20kept%20an%20Excel%20spreadsheet%20as%20a%20data%20source%2C%20where%20I%20kept%20track%20of%20some%20numbers%20that%20were%20not%20available%20in%20that%20form%20on%20the%20web%20site.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStarting%20out%20with%20Excel%2C%20I%20used%20a%20map%20chart%20with%20colour%20shading%20to%20show%20the%20numbers%20for%20each%20New%20Zealand%20region.%20That%20was%20already%20challenging.%20New%20Zealand's%20map%20chart%20needs%20a%20lot%20of%20tweaking%20to%20be%20integrated%20in%20a%20dashboard.%20The%20source%20data%20required%20a%20lot%20of%20cleaning%20up%20and%20massaging%20in%20Excel's%20Power%20Query%2C%20but%20I%20finally%20got%20a%20usable%20map%20and%20arranged%20a%20few%20charts%20around%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel%20dashboard.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190534i4B1FBCC383A2A240%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Excel%20dashboard.png%22%20alt%3D%22Excel%20dashboard.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20the%20Ministry%20of%20Health%20changed%20the%20reporting%20units%20to%20grouping%20by%20District%20Health%20Boards%2C%20which%20don't%20overlap%20with%20the%20local%20government%20region%20boundaries%2C%20I%20switched%20to%20Power%20BI%2C%20so%20I%20could%20plot%20points%20on%20the%20map%20instead%20of%20regions.%20Using%20Power%20BI%20also%20meant%20that%20I%20could%20publish%20the%20dashboard%20on%20the%20internet%20and%20let%20people%20interact%20with%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20could%20re-use%20the%20Power%20Query%20code%20from%20Excel%20in%20Power%20BI%2C%20so%20the%20online%20version%20was%20configured%20very%20quickly.%20What%20followed%20was%20a%20series%20of%20query%20adjustments%20as%20the%20Ministry%20of%20Health%20changed%20table%20positions%2C%20table%20structure%2C%20column%20names%2C%20etc.%2C%20almost%20daily.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20used%20Power%20Query%20commands%20for%20data%20cleanup%2C%20a%20few%20lookup%20tables%20in%20the%20data%20model%20and%20a%20lot%20of%20checking%20every%20day%20to%20make%20sure%20my%20dashboard%20works%20without%20errors.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Power%20BI%20dashboard.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190535i9B93F7A928BC312F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Power%20BI%20dashboard.png%22%20alt%3D%22Power%20BI%20dashboard.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20access%20the%20live%20dashboard%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fbit.ly%2FCovid19NZ%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fbit.ly%2FCovid19NZ%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EFor%20a%20more%20detailed%20look%20under%20the%20hood%20of%20this%20dashboard%2C%20watch%20this%20video.%20It%20is%20a%20recording%20of%20a%20session%20from%20%3CA%20href%3D%22https%3A%2F%2Fwww.m365may.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EM365May.com%3C%2FA%3E%2C%20a%20free%20online%20conference%20that%20runs%20sessions%20throughout%20the%20month%20of%20May%202020.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3C%2FP%3E%3CDIV%20class%3D%22video-embed-center%20video-embed%22%3E%3CIFRAME%20class%3D%22embedly-embed%22%20src%3D%22https%3A%2F%2Fcdn.embedly.com%2Fwidgets%2Fmedia.html%3Fsrc%3Dhttps%253A%252F%252Fwww.youtube.com%252Fembed%252FZp53a3co6WQ%253Ffeature%253Doembed%26amp%3Bdisplay_name%3DYouTube%26amp%3Burl%3Dhttps%253A%252F%252Fwww.youtube.com%252Fwatch%253Fv%253DZp53a3co6WQ%26amp%3Bimage%3Dhttps%253A%252F%252Fi.ytimg.com%252Fvi%252FZp53a3co6WQ%252Fhqdefault.jpg%26amp%3Bkey%3Db0d40caa4f094c68be7c29880b16f56e%26amp%3Btype%3Dtext%252Fhtml%26amp%3Bschema%3Dyoutube%22%20width%3D%22600%22%20height%3D%22337%22%20scrolling%3D%22no%22%20title%3D%22YouTube%20embed%22%20frameborder%3D%220%22%20allow%3D%22autoplay%3B%20fullscreen%22%20allowfullscreen%3D%22true%22%3E%3C%2FIFRAME%3E%3C%2FDIV%3E%3CP%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1378700%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20Covid-19%20numbers%20in%20New%20Zealand%20were%20still%20low%2C%20there%20were%20no%20charts%20or%20dashboards%20online%20to%20help%20visualise%20the%20situation.%20So%20I%20set%20out%20to%20make%20my%20own.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1378700%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20for%20good%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1388621%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20for%20good%3A%20Building%20a%20dashboard%20to%20help%20visualize%20Covid-19%20infection%20in%20New%20Zealand%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1388621%22%20slang%3D%22en-US%22%3E%3CP%3EAmazing...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1389089%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20for%20good%3A%20Building%20a%20dashboard%20to%20help%20visualize%20Covid-19%20infection%20in%20New%20Zealand%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1389089%22%20slang%3D%22en-US%22%3E%3CP%3EBeautifully%20done!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425498%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20for%20good%3A%20Building%20a%20dashboard%20to%20help%20visualize%20Covid-19%20infection%20in%20New%20Zealand%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425498%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20brilliant%2C%20thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E

Over the past few weeks, I’ve been building and updating a dashboard that shows the development of the Covid-19 infection in New Zealand. With the ever-changing data sources, I ran into quite a few challenges and had to come up with some workarounds. I want to share with you some of my approaches and decisions. This article gives just a short overview. Watch the video below if you want to go into detail.

 

My main data source is the New Zealand Ministry of Health web site. The format and presentation of the data on that site changed a lot and I needed to update my queries almost daily. I also kept an Excel spreadsheet as a data source, where I kept track of some numbers that were not available in that form on the web site.

 

Starting out with Excel, I used a map chart with colour shading to show the numbers for each New Zealand region. That was already challenging. New Zealand's map chart needs a lot of tweaking to be integrated in a dashboard. The source data required a lot of cleaning up and massaging in Excel's Power Query, but I finally got a usable map and arranged a few charts around it.

 

Excel dashboard.png

 

When the Ministry of Health changed the reporting units to grouping by District Health Boards, which don't overlap with the local government region boundaries, I switched to Power BI, so I could plot points on the map instead of regions. Using Power BI also meant that I could publish the dashboard on the internet and let people interact with it.

 

I could re-use the Power Query code from Excel in Power BI, so the online version was configured very quickly. What followed was a series of query adjustments as the Ministry of Health changed table positions, table structure, column names, etc., almost daily.

 

I used Power Query commands for data cleanup, a few lookup tables in the data model and a lot of checking every day to make sure my dashboard works without errors.

 

Power BI dashboard.png

 

You can access the live dashboard here: https://bit.ly/Covid19NZ


For a more detailed look under the hood of this dashboard, watch this video. It is a recording of a session from M365May.com, a free online conference that runs sessions throughout the month of May 2020.

 

 

 

 

 

3 Comments
Senior Member

Amazing...

Occasional Visitor

Beautifully done!

Visitor

This is brilliant, thank you