Microsoft Excel: Create a State / Region Lookup Table in 5 Minutes with Power Query

Just Grab Online Link and Shape The Table

Use Power Query to Create a State / Region Lookup Table

If you would like to download the Excel file I refer to below — you can get it here.

Introduction

A common “reference” table needed when you are working with data is a STATEs of the USA related lookup table.

The simplest form of this is to group the states into the four regions of the country — Northeast, South, Midwest, and West.

With easy access to this data table, I have the ability to quickly group any data I work with that is broken down into states into a “higher level” region analysis (or Total Data into a more granular Region level view).

I will explain how to do this using the common data analytics approach of “ELTR” — Extract, Transform, Load, Report.

How It’s Done

Extract Data (Find Source and Import Data) — 1:00 Minute

In this case, I do a Google search and select a data set that can be easily converted to the format I want. There are tons of resources — find and pick one you are comfortable with.

Google Search Identified Data Source

This is an Excel file that the US Census Bureau hosts. I’m simply going to get the URL of this file and use it in Power Query.

The “Source” file looks like this:

State / Region Source file at US Census Bureau

We want to query data from “the web”. Paste the URL link and click OK. Review the Table Names and select the table you want to import.

Enter URL and click OK to Query web data

In Excel, the initial Power Query “edit” screen view is here.

Initial view of Data Source — Now we “shape” the data

Transform (Shape the Data Table to our Needs) — 3:00 Minutes

• Modify the field types and move the name column to the first column.

Modify Field Types and Move Name Column to First Column

• Add a “Custom” column to assign the Region Name to each row. Then use the “Fill” command to populate EVERY row of the data set.

Create Custom Column and Populate Region Data

• To create the column, we use the “Conditional Column” function…

“Add Conditional Column” and populate Region for each State

• Then, using the “State (FIPS)” column, filter out the “0” values. These are not STATE related records, so I am removing them from the final lookup table.

Filter Out Records that Aren’t States — All 0’s

• Now, the table shows just States and Regions records.

Just State records remain in this Table

Just a couple more steps to finalize my table —

• Sort alphabetically by State,
• Remove the # reference columns — not needed anymore.

Here is the final table (for now).

Final State / Region Reference Table — in Query Edit Mode

Load (Bring Table Into Excel for Use as a Lookup Table) — 1:00 Minute

At this point, I can load the Table into an Excel worksheet OR load it into the “Data Model” (in the background of Excel workbook).

Load into Data Model

Choose Load Option — in This Case, it is set to Load Into The Excel “Data Model”

Load as Table Into Excel

Final Lookup Table in Imported Into Excel

There you go — 5 minutes from Idea to Finished Table. Power Query makes it that simple!

About Don

“It’s time for different”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere