Analyze US Election Candidates in Excel and #PowerQuery

As promised here, today we will build a dashboard that analyzes Facebook Insights on US election candidates.

As a teaser, here is a screenshot of a 3D Map you will be able to create when we are done:

To start this tutorial, you should first follow Data Chant walkthrough here or skip it and start with this workbook as a starting point.

Cool new feature – Copy & Paste queries

We will start by downloading this workbook, open the Workbook Queries pane (Data –> Show Queries), right click on the query Storytellers and select Copy.

Note: Copy and Paste of queries is a new feature in Excel & Power BI. If you don’t have it in your Excel, please ensure you are on the latest version – If you are on Excel 2013, download latest Power Query Add-In. If you are on Excel 2016, ensure you are on the First Release branch (Enterprise plans), or on the latest Consumer Branch.

Now open a new workbook, and paste the query to the Workbook Queries Pane.

Unexpected experience warning: If you paste the query by pressing CTRL+V, you will get the M expression code in the grid. Instead, click Data –> Show Queries, right click anywhere inside the pane, and select Paste.

The paste operation automatically takes all the load settings from the original query. In our case, it loads the data into the Data Model. As we don’t want to load the data yet, we will unload the data.

Right click the query Storytellers and then the click Load To. In the Load To window uncheck Add this to Data Model, and click Load.

Click Continue on the next warning dialog.

Transforming the basic query into a query function

Right click the query Storytellers and then the click Edit. In the Query Editor window, click Advanced Editor.

Modify the first three lines of the M expression according to this screenshot, and click Done.

 

You can also copy and paste the code from here, after you remove the original 3 lines (starting from let and ending in Source)

(facebookPage) =>

let
    sincePeriod = Text.From(Date.From(DateTimeZone.FixedUtcNow() – Duration.From(30))),

    Source = Facebook.Graph(“https://graph.facebook.com/v2.2/” & facebookPage & “/insights/page_storytellers_by_country?since=” & sincePeriod ),

The modification above will turn the query into a query function that accepts a Facebook page name and return the data since the last 30 days.

Now let’s rename the query to GetDataByPage (You can rename a query in the Query Editor –> Query Settings –> Properties –> Name).

Click Close & Load to save the query function.

Copy & Paste the following content to the workbook:

Brand Facebook Page Party
Donald Trump DonaldTrump Republicans
Hillary Clinton hillaryclinton Democrats
Ted Cruz tedcruzpage Republicans
John Kasich JohnKasich Republicans
Marco Rubio MarcoRubio Republicans
Bernie Sanders senatorsanders Democrats

Select any of the cells, and click Data –> From Table.

In the Create Table dialog check My table has headers, and click OK.

The Query Editor window will appear. By now you can see the table of candidates with their Facebook page object name and their political party.

In Add Column tab click Add Custom Column.

The Add Custom Column window will appear. Enter the expression below, and click OK:

= GetDataByPage([Facebook Page])

This custom column formula will take the Facebook Page name from the second column and send it as an input parameter to the query function GetDataByPage that we have created earlier.

Click Continue in the yellow bar.

In Privacy levels window, select Public for the two data sources and click Save.

You can see that we now have a new column that contains Table objects. Each object contains the Facebook insights data of the relevant candidate.

Let’s click  the expand button (highlighted in the following screenshot):

Uncheck Use original column name as prefix, and click OK.

Change the type of column Shares to Whole Number. There are many ways to do it. For example: Right click on the column header, select Change Type and then select Whole Number.

Change the type of column Date. By now you know how 🙂 

Rename the query to Candidate Results.

In Home tab, click Close & Load To…

In the Load To window select Only Create Connection, and check Add this data to the Data Model, then click Load.

Let’s Start Visualizing the US Election Candidate Data

We will start by creating few PivotCharts on our data which was loaded to the Data Model.

In Insert tab, click PivotChart.

Click OK in Create PivotChart dialog.

In PivotChart Fields pane, expand the table Candidate Results.

Drag and drop the field Party to the Legend (Series) box, then drag and drop the field Shares to the Values box.

Insert a new PivotChart, and drag and drop Brand to Legend Series, Date to Axis (Categories) and Shares to Values.

Notice that a new field was automatically created: Date (Month). This is the result of a new feature in Excel 2016 (You can read here about it).

Click the + button in the chart (highlighted below).

In the Design contextual ribbon of the PivotChart, click Change Chart Type, and in the Change Chart Type dialog select Line and click OK.

 

Before we can sit back, relax and enjoy our new visuals and insights on the Facebook fans of US election candidates, we  should perform an important advanced step that will fix a limitation in our query.

The static country codes problem

If you followed my blog here, you know that the query that we generated contains static country list, which doesn’t include all the countries. The static list was created when we loaded the Excel Facebook Page data on our last tutorial here.

To resolve the problem, we have created two queries that can help us.

Note: The next parts will look like voodoo if you didn’t read this tutorial . You are encouraged to read it and get a step-by-step instructions. It was written for Power BI, but the steps are 100% the same in Excel.

Make sure you open the workbook we used in this tutorial. Create a blank query (Data –> New Query –> From Other Sources –> Blank Query)

Name the new query as Country Codes.

Copy and paste the following expression to the Advanced Editor window:

let
Source = Web.Page(Web.Contents(“https://en.wikipedia.org/wiki/ISO_3166-1”)),
Data0 = Source{0}[Data],
#”Changed Type” = Table.TransformColumnTypes(Data0,{{“English short name (upper/lower case)”, type text}, {“Alpha-2 code”, type text}, {“Alpha-3 code”, type text}, {“Numeric code”, Int64.Type}, {“Link to ISO 3166-2 subdivision codes”, type text}}),
#”Removed Other Columns” = Table.SelectColumns(#”Changed Type”,{“English short name (upper/lower case)”, “Alpha-2 code”}),
#”Renamed Columns” = Table.RenameColumns(#”Removed Other Columns”,{{“English short name (upper/lower case)”, “Country”}, {“Alpha-2 code”, “Code”}}),
#”Replaced Value” = Table.ReplaceValue(#”Renamed Columns”,”United Kingdom of Great Britain and Northern Ireland”,”United Kingdom”,Replacer.ReplaceText,{“Country”})
in
#”Replaced Value”

Create a second blank query and name it CodeList.

Copy and paste the following expression to the Advanced Editor window:

let
Source = #”Country Codes”,
#”Removed Columns” = Table.RemoveColumns(Source,{“Country”}),
Code = #”Removed Columns”[Code]
in
Code

Now edit the query function GetDataByPage by opening its Advanced Editor, and modify the line that starts with:

#”Expanded values.value” = Table.ExpandRecordColumn(#”Reordered Columns”, “values.value”, …

Add CodeList to that line above as follows:

#”Expanded values.value” = Table.ExpandRecordColumn(#”Reordered Columns”, “values.value”, CodeList),

Click Done in the Advanced Editor, and click Home –> Close & Load.

After a refresh of the data, you now have a dynamic list of countries that will scale up the queries to get all the data from Facebook from all the countries – and not just the countries that has Excel fans who talk on Excel 🙂

To spice up the dashboard, I have added a 3D Map (aka Power Map). You can download the workbook here (The link is embedded in the TechNet Gallery contribution description).

If you have interesting insights, please share in the comments.

Hope you enjoyed this tutorial.

Follow me on Twitter & Facebook