Structure
We now have our data downloaded from the London Data Store however it is not in a useable format for our future analysis. We can use PowerBI is to get the data into a structure which is machine readable.
Please note allot of the steps we have saved for SQL could be done here in PowerBI. The steps have not been completed here just to demonstrate the use and capabilities of both platforms.
UK House Price Index
Upon analysis of this file, we will want to take the data from the tabs Sales volume and Average price. We must therefore convert these into tables so they can be picked up by Power Query. We need to pivot out columns so our data becomes long data as opposed to wide. Our next step is then to merge two tables we have chosen to use.
I will show the merge step here but will not show it repeated for the other workbooks.
Earnings by Place of Residence
We will take the data from the tabs for hourly pay for males and females. We must therefore convert these into tables so they can be picked up by Power Query. We need to pivot out columns so our data becomes long data as opposed to wide. Once we have done this, we can filter the rows out that contain the conf% data. We can apply the above steps quickly to the other table using the advanced editor. Our final step is then to merge two tables we have chosen to use.
Household Waste Recycling Rates
This data is already pretty clean. We will however note to add borough flags in SQL later – a Left Outer Join should do the trick (this being the right table to ensure no additional boroughs are picked up which we have no other data for). The year column is listed in a different format – this will need correcting. We shall also do this later in SQL.
Jobs and Job Density
Much the same as the recycling data, machine readable but the year column is the correct format.
Dwellings
We have a similar situation here as we have seen before. We need to transpose our data, merge a table and add borough flags. We’ll turn everything into tables and use the power query editor to repeat some of the steps described previously.
Age Tables
We can again copy the steps above as we have data in a similar layout but with the added bonus of a borough flag this time around!
Personal Well-being
We have a bit more of a challenge with this data however there is a tab that contains the mean scores for each year, removing the groupings we see here. We will use this tab as a base point. We then will remove the columns that are blank, transpose the data to make it long and machine readable. We have a fair few queries to merge this time around. Once they are all neatly formatted, this is a simple task.
