Excel looks for certain patterns when processing data for Excel Power Map, and this can create limitations.

I ran into a few of these trying to bring data in from this page.  I wanted to see the parties of the presidential candidates who won election by state each year. The first table, titled “Chronological” has that information.

After downloading Power Map Preview and Power Query (see this previous post). I selected From Web for my data source and entered the Wikipedia article’s address.

Power Query found 3 tables.  The Chronological table, the Analytical table, and the table of links at the bottom of the page.  The chronological table was most interesting to me, so I brought that one in.

Why this table won’t work

After doing some minimal cleanup of the data, like deleting empty columns and getting the column headers right, I brought it into Excel Power Map (Under the Insert tab).

Excel identified the state properly for the location, but nothing else made sense. There were several problems.

• Excel looks for category, time, and location data in columns not rows. This data has the dates in the first row as the column headers. I could have had each year be a category of data, but that’s not very useful.
• Excel also wants full dates for its time variable, and years before 1900 are not recognized.
• The data uses colors and bold to visually show which party won in that state and won nationally. Excel doesn’t accept this information.

What data would work

In this example, Excel needs separate column headers for State, Year, and Result (who won).  Ideally, a separate row for each election result would work best, as in the example data below.

I reworked a small section of the data to form a Power Map.