Building A Power BI (COVID) Report From Scratch - Excelerator BI

Building A Power BI (COVID) Report From Scratch

As promised, today I am sharing with you the process I went through to build the COVID report I shared in my last article. One of the best ways to learn and improve your Power BI skills is to practice. The best way to practice is to find something you care about (work or home) and build something useful. I built this report because I care about the lack of transparency and lack of full information in Australia about what is happening with COVID.

Watch Me Do It

The video today is simply a walk through of what I did to build this report. It is actually a reproduction – I did it first, then recorded a video so you can see what I did. I have tried to keep it “warts and all” so that you can see where I made mistakes, etc. It is quite a long video (36 mins) but I hope you find it a good learning resource.

Summary Of Key Learnings

If you would like to know what I cover in the video, here is a brief list.

  • I created a staging query connected to the source
  • Inspected the new data source to understand its structure
  • Built a star schema
  • Unpivoted the data into a long skinny table
  • Found and fixed errors in the transformed data
  • Realised I should remove zeros from the data
  • I showed how to enumerate a list of dates in Power Query
  • I showed how to enter today’s date in Power Query

Interactive Report V1.0

Here is a link to the live, interactive report.  This will auto refresh every day. I will show you how I did that in my next article.

Wrap Up and Next Steps

Here is a copy of the workbook so you can take a look for yourself.

You will need to download a copy of the data yourself from this link and reconnect the workbook to your local copy.

I have already started to improve my report, and I plan to record more videos in the coming weeks to show you the process of continuous improvement to make version 1.0 bigger and better. Keep an eye out for those videos in the coming weeks.

Please let me know in the comments below if you like this style of video so I can get some feedback on that.

5 thoughts on “Building A Power BI (COVID) Report From Scratch”

  1. Great video, thank you Matt! So helpful to hear and see your thought process and steps as you work through the development of the data load, transformation, and report.

  2. Very awesome Matt. Thank you.
    Loved learning new tricks.

    At about 5:13 in te video you said ….
    “Turn off the load so you have a permanent connection to the CSV file”.

    ummm what now? Why? – Did you do this just so it doesn’t reload the data when you click Close & Apply? I haven’t heard of this before.

    Also… loved the PowerQuery steps… especially adding Phase to the date table.
    And as usual your DAX demos are fantastic.

    – David

    1. Hey David. Sorry about that – my words were a bit vague/imprecise. Whether you turn off load or not, the query keeps a permanent connection to the file. What I should have said is “turn off the load. This keeps the permanent connection to the file but simply doesn’t load the data to the model in its raw form”.

      The reason I do this is so I can easily see the full raw version of the file if I want to go back to it, plus I can build many new queries off the raw file should I choose to down the track. I learnt this from Ken Puls in the Power Query Academy course, and I do this as my standard operating approach now.

      Adding the phases – yes, one of my favourite tricks. The trouble is, when you have powerful tricks up your sleeve, sometimes you miss the obvious. See Kamil’s comment below for a much simpler method.

  3. Kamil Kaleciński

    Great tutorial, Matt. I would pass it to everyone who ask me to help starting his PBI adventure. Just to make things simpler, instead of building lists for phases, I would just merge it for start dates and then fill down for the rest of (sorted) calendar. Just to phase knowledge for beginners :).

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top