Preppin’ Data — 2019W17

A wonderful topic as I am working on this on the day of US election result. Here is a link to read more — How the world votes

Input

Solution Steps

We need to create 3 different flows and union the single line results to obtain the required output structure.

First past the post

1. This approach only considers a single preference from each voter and thus we need to extract only first letter of the preferences and count the no. of votes
2. Take Left([Voting Preference],1)
3. Drop Voter and Voting Preferences column and add a column with a value of 1 so that its easier to aggregate on that column
4. Aggregate on the First preference, Rank the total votes and filter for Rank 1

Alternate Vote

This is the most complicated approach among the three.

1. Take Left(Voting Preference,1) and drop all other columns
2. Aggregate the number of rows to calculate the overall votes. Create Total Votes and Max Votes LOD Fields to calculate if Max votes > Total Votes/2. If Yes, thats your winner. But here it is not.
3. If not, Create a calculated field to Identify lowest number of voted candidate.
4. This is the tricky part. Now join this with the original datasource and remove the least voted candidate from all preferences and then redo the process of counting.

Borda Count

1. This approach assigns points based to all preferences in descending order
2. Split the voting preference into 3 columns and name the columns First, Second and Third accordingly
3. To be able to aggregate the overall points for each candidate, the data should be in rows so that it can be aggregated. Use Pivot — Columns to Rows.
4. Write a calculated field to award points to the positions based on the column names.
5. Aggregate after removing the voter column. Rank the vote count and filter for rank = 1

Output

Prep Flow

The flow can be accessed here — Github Link