Election in BC Part 1 – Preparing the data

There were elections and I felt like our beloved province of British Colombia didn’t get the necessary attention and doesn’t have it’s own data visualization solution! There was a small one in the Vancouver sun: but it is a custom based solution by their talented Graphic designer: Lucas Timmons (GitHub, Twitter: @lucastimmons) not a PowerBI or Tableau! Not to worry BC Yana to the rescue!

What shall I do? how to fill the missing data? so the obvious choice might be export everything to Excel or even just copy to Excel and duplicate the result. Let us not despair, this is exactly what DAX & M are for!

I have added a new custom column with the following function written to add it in DAX:

Than I have used the Fill option, from the Transform ribbon tab to fill down all the Null cells. The problem that accord when the Fill down didn’t work in the original column was that those cells did not have the null value to begin with, means they are not empty and cannot be filled according to the definitions.

In the View Ribbon tab, I can see all the data manipulation I have completed so far using M:

I have also removed the total 100% ballot box number as the bottom value for each community. This value we can get later in the 2nd data set by party and connect them by community. Or simply add a measure to calculate it.

I haven’t used code this time, only the existing PowerBI options.

Transform ribbon tab->Split the column [Electoral District]

Used the custom split with the custom value “of” (common for all the ballot boxes numbers there)

Got 2 columns when in the target columns had “null” value for all the rows without the ballot counting

Used the top row filter to simply filter out all the rows that didn’t have null

In PowerBI, now that I don’t need the extra data manipulation column, I just got rid of it 🙂 delete column it is.

The current M code for the whole operation looks like this: (It’s different for a data source that didn’t have to be changed from HTML, the references might contain other commands for different data source data types)

Entered to edit the data and split the column with the ballet boxes. I only need their number not the real time count since the election are over.

The data manipulation in the query mode I have used: Split cells,
the split value was: “of” since the values are: “35 of 35”. I ended up with 2 columns containing the same number. so I deleted one of them.

I have made sure that all the columns are from the type Decimal number, since I really don’t want to upload a column containing numbers as a text column and wonder why things keep not working? 🙂

Next I have uploaded the data to the data set as well.

Now I have 2 Tables containing the data – so easy now the visualization part begins!