Test of Embedding Power BI

https://app.powerbi.com/view?r=eyJrIjoiMWQzZTIwYzgtNTViYS00NmVjLTk3ZDMtOWM0MGMxMzViZjY2IiwidCI6IjgzZmYxNGE2LWViNmItNDI1MC1iNzQ1LTIxYmVhNjYyNTNkYyIsImMiOjN9

Advertisements

How can one Map Census Block Data in Microsoft Excel Power Map?

The census contains a wealth of demographic data down to a very small portion of a county called a census block.  It also contains data at the zip code level as well.  Power Map can map zip codes.  Unfortunately zip codes cross county boundaries, so data can’t easily be rolled up to the county level.  It would be nice if Power Map could recognize census block data geographically.

Power Map does support Latitude and Longitude.  If we could translate the block level data to a latitude and longitude than we could map the data.  After much searching of the Census website I was able to find a source for the latitude and longitudes of the blocks based on centers of population.

If you go to the Geography Reference page on the census.gov web site you will find listed Centers of Population.

https://www.census.gov/geo/reference/centersofpop.html

Centers of Population 1

Click on Data Files and select Centers of Population by Block group.  You have to select a state.  I picked Florida.

Centers of Population 2

This will open up a text file that contains comma delimited data in the format shown below.

Centers of Population 3

We can now use this data to map the census blocks.  Save the data to a file on your hard drive.  Use the From File option in Power Query and

select either CSV or text file and load the data.  It will load without headers.

Census Block Power BI 2

Rename the columns.

Census Block Power BI 4

Add a new Column for GeoCode.   It is basically a combination of the state, county, tract and Block codes.

Census Block Power BI 5

This identifier will match the identifier used in the census data files.  Convert all columns to numbers.

Census Block Power BI 6

Close and Load this data.  Now go to insert a map.

Census Block Power Map 1

Launch Power Map.  The Data will load and recognize the latitude and longitude.

Census Block Power Map 2

Select next at the bottom right and then check population.  Tilt down to see the population.

Census Block Power Map 3

The data is now available to tie to other census data.  Below is an example where I tied in the level of education for the Duval County, Clay County, and St John County. The map is showing the number of people with college educations, bachelor degree or higher.

Census Block Power Map 4