First off, hope everyone is doing well in this very interesting times. As i was watching the news, I kept seeing this dashboard from Johns Hopkins
I figured it would be a good exercise to see if that could be replicated/improved. Step 1 was to find the data. Luckily they provided a link to github where you can find the data:
navigate down and you can download a csv for each day. Now, I dont really want to download a file manually every day, so set out to automate that.
First step was to get a list of csv files. Easy enough, connect to a web page and do some transformations and get the following:
Now that we have the file names, how do we get the data associated with those files? If you click into one specific file there is a button for “raw” which does:
Armed with that I can create a function that will change the last part of that url to that of our file list.
= (FileName as text)=> let Source = Csv.Document(Web.Contents("https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/"&FileName),[Delimiter=",", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]) in Source
the file name will be called back in our 1st table. Invoke that function and you get a table for each csv file
Expand out and do some cleaning up and you are good.
Then from there just the normal dax and dasboarding type things, but the data will update accordingly.
Here’s the file:
COVID 19 Data.pbix (13.3 KB)