How you can get data stored on GitHub in CSV format. How to create a connection to more files and then append them together. How to create 1 file from 3 files. If you want to know how to do it follow this quick exercise...
[1] click on the first CSV file called Confirmed.csv and then [2] click on the RAW button.
We will use this URL address to get data from the web page. These 3 URL addresses will be our WEB data source. [Confirmed.csv, Deaths.csv, Recovered.csv].
Place the first URL into Get Data >WEB field and click connect to access the web content.
We must transform the data, do some data cleansing and upivote columns. Click on the TRANSFORM DATA button.
In the field for APPLIED STEPS click on the SOURCEto see how the data have got imported. You can clearly see that the value for the number of columns is hardcoded. I don’t want that. I want all the columns every time I click on the refresh button. Therefore, in the next step I delete this part of the code.
Click on the table icon in upper-left part of the table and from the options pick the one which says “Use First row as Headers”.
I want to create a Location table. I will do it by duplicating the first table which I called Confirmed.
After creating Location table, I keep just first 4 columns and the rest will be deleted. Mark columns you want to keep and right click on the highlighted part and choose REMOVE OTHER COLUMNS.
I want to add Custom Column and create Location column which is going to be a key column. This column is a concatenation of Country and Province columns. You can insert those 2 columns from the AVAILABLE COLUMNS box and insert a concatenation sign between them [&” “&].
Repeat the same process for Confirmed table. Highlight Province/Stateand Country/Region columns, right click > Merge Columns. You can also delete Lat and Long columns form the Confirmed table so you will avoid unnecessary duplication in those tables.
In the next step I want to unpivot Confirmed table. Click on the LocationKey column go to the Transform ribbon and choose Unpivot Other Columns.
You will get a table like this.
In the next few steps, I would like to get Recovered and Deaths tables. DUPLICATE Confirmed table. Go into APPLIED STEPS panel on the right site, click on the SOURCE step on the very top and change the URL address like this. Last part of it will be Recoved.csv.
Click on the last applied step to apply them all at once.
In the next steps:
1.DUPLICATE Recovered table
2. Change the URL address, write down Deaths.csv on the end
3. Apply the last step to apply them all at once
4. Change the last column name from Recovered to Deaths
Append Recovered and Deaths tables to the Confirmed table. On the left panel with QUERIES click on the Confirmed table. Go to HOMEribbon, click on APPEND QUERRIES choose APPEND QUERIES. Or you can leave them as a separate table if you like.
As we have append Recovered and Deaths tables to the Confirmed table, we don’t need them anymore. Click on both table and uncheck ENABLE LOAD.
Comments