Powery Query from Politico for US election

[es12399]

Would really appreciate some urgent help on this one...as it is only going to be useful for 1 more day.

I am trying to import a live county by county vote count update from politico (Live election results: 2020 Florida results) into excel. Note it is down the bottom of this web page and you have to click "See all 67 counties" to view all the data

However when I run the power query it only finds 3 columns (instead of 5) and only returns 10 rows (instead of the 67 or so there should be)

Can anyone help with this? Thanks very much.

 

[smozgur]

Welcome to the MrExcel Message Board!

The source page is loading the data dynamically by using XHR. That's why you can't access all data by capturing the table in the HTML document model. However, you can find the Ajax XHR calls occurring in the background, and reveal the actual source, then get the data you need.

For this particular page, it is using three different end-points (many others in the background, but I think these are the ones building that table):

1. County names: /2020-election/data/general-election-results/metadata/12/county-names.meta.json
2. Candidates: /2020-election/data/general-election-results/metadata/12/potus.meta.json
3. Votes by Counties for each Candidate: /2020-election/data/general-election-results/live-results/12/potus-counties.json

What needs to be done is retrieving these data sets, and merging/joining them. One of the ways of doing that could be the following M code. This is just a sample as a starting point as I am sure you can adjust the resulting table as you need even with additional data retrieved by the other end-points.

Create a new blank query (Data->Get Data->From Other Sources->Blank Query), click Advanced Editor in the Power Query toolbar, and copy and paste the following code.
Note: You'll either need to disable Privacy Levels check for the workbook, or set Public value for the privacy level for the source website. It will ask for your confirmation as soon as you try to execute the M code.

let

    JsonCounties = Json.Document(Web.Contents("https://www.politico.com/2020-election/data/general-election-results/metadata/12/county-names.meta.json")),
    TableCounties = Record.ToTable(JsonCounties),

    JsonVotes = Json.Document(Web.Contents("https://www.politico.com/2020-election/data/general-election-results/live-results/12/potus-counties.json")), 
    TableVotes = Table.ExpandRecordColumn(
            Table.FromList(JsonVotes[races], Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
            "Column1", 
            {"countyFips","candidates"}
        ),

    JsonCandidates = Json.Document(Web.Contents("https://www.politico.com/2020-election/data/general-election-results/metadata/12/potus.meta.json")),
    TableCandidates = Table.ExpandRecordColumn(
            Table.FromList(JsonCandidates[candidates], Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
            "Column1", 
            {"candidateID", "fullName"}
        ),        

    MergeCountyAndVote = Table.NestedJoin(
            TableCounties, 
            {"Name"}, 
            TableVotes, 
            {"countyFips"}, 
            "potus-counties", 
            JoinKind.LeftOuter
        ),
                            
    ExpandCandidateColumn = Table.ExpandTableColumn(
            MergeCountyAndVote, 
            "potus-counties", 
            {"candidates"}
        ),
        
    ExpandCandidateList = Table.ExpandListColumn(
            ExpandCandidateColumn, 
            "candidates"
        ),
        
    ExpandCandidateVotes = Table.ExpandRecordColumn(
            ExpandCandidateList, 
            "candidates", 
            {"candidateID", "vote"}
        ),

    MergeCandidateVotes = Table.NestedJoin(
            ExpandCandidateVotes, 
            {"candidateID"}, 
            TableCandidates, 
            {"candidateID"}, 
            "potus meta", 
            JoinKind.LeftOuter
        ),

    ExpandCandidateNamesColumn = Table.ExpandTableColumn(
            MergeCandidateVotes, 
            "potus meta", 
            {"fullName"}
        ),

    RemoveColumns = Table.RemoveColumns(
            ExpandCandidateNamesColumn,
            {"candidateID"}
        ),
        
    PivotByCandidate = Table.Pivot(
            RemoveColumns, 
            List.Distinct(RemoveColumns[fullName]), 
            "fullName", "vote", 
            List.Sum
        ),
    
    Result = Table.RemoveColumns(PivotByCandidate,{"Name"})
in
    Result

I hope this gives an idea as a starting point.

 

[es12399]

This is brilliant. A little beyond my ability but thanks very much for your help.