The City of Toronto has a 311 service for anyone requiring information or service. As part of their open data initiative, the City of Toronto has published an Excel file of a chunk of their service requests. The Excel file has 466,000 records mapping to each call that was made to the 311 service between June 2012 and July 2013.
The following shows how PowerPivot and PowerView and be used to visualize the data.
LOADING IN THE DATA INTO POWERPIVOT
One of the easiest ways to move data into PowerPivot is to cut and paste from Excel.
Copy the table and then go to PowerPivot and hit Paste, and a dialog comes up prompting you to create a new PowerPivot table with your pasted data.
Provide a table name and your PowerPivot table is now automatically created.
ADDING SOMETHING TO COUNT
We want to be able to count the number of requests, but we’re missing a field that represents the 1 request for each row. I just added a calculated field called “Number of Requests” and set it equal to 1 for each row. Now when we aggregate based on the other columns, we’ll use this as our core value to sum in our roll-ups.
CLEANING UP THE POSTAL CODE DATA
In this dataset, most of the service locations are by the first three letters of the Postal Code but in some cases they are the street address. To simplify, I created a calculated column that wiped out any of the street addresses and set them to BLANK().
The formula I used to create the calculated column was:
=If(len([SERVICE REQUEST LOCATION])<4, [SERVICE REQUEST LOCATION], Blank())
CLEANING UP THE CATEGORIES
There are 384 unique Service Request Types in the dataset, which is far too many to be useful for visualization or reporting. I created a table in Excel that mapped each of these categories to a broader roll-up category. I then copied and pasted this map back into PowerPivot as a separate Service Request Type Group table.
Instead of seeing every micro service request type, we can report using the broader rollup group.
CREATING A HIERARCHY
I want to be able to do a drill down from Postal Code to Service Request Type Group. First, I first created a relationship between my two tables. This relates the Service Request Type to the parent Service Request Type Roll-up Group.
The Service Request Type Roll-up Group field and the Postal Code field are in two different tables and PowerPivot doesn’t support creating hierarchies with multiple tables. The solution is to create a look up field in the 311 Service Requests table as a calculated column using the Related() function. PowerPivot will even help you with the syntax by finding all possible fields and tables for you!
Now we have our field and we can create a hierarchy within the same table. You create hierarchies in the Diagram View.
VISUALIZING THE DATA THROUGH POWERVIEW
Now that our data is prepared, we can visualize the data in PowerView. As expected, PowerView provides us the list of fields from our PowerPivot model.
We can use the hierarchy we created earlier to enable drill down from Postal Code to type of request either through a Matrix or Bar Chart. I like the Tiles option as a way to scroll through each postal code.
VISUALIZING THE DATA THROUGH POWERMAP
The postal codes can be also mapped using Microsoft’s new Power Map (you will need the Power Map Addin as part of the Power BI Preview). Here is my data mapped by postal code as a bar chart with the stacks divided by request type.
