Let’s Excel
Published in

Let’s Excel

Data Analytics & Visualizations — Replicating the Tableau Example In Excel — Part 1

Extracting The Dataset Into The Excel Data Model

Download Excel File in ZIP format here

Understanding The Data Source

“The data set used comes from the National Center for Charitable Statistics(NCCS) website (http://nccs.urban.org/), which maintains the IRS Business Master Files (BMF) using data derived from the IRS Forms 1023 and 1024 of the over 1.5 million U.S. not-for-profit organizations that file IRS Form 990. The authors chose this data set to demonstrate Tableau’s ability to handle big data and the types of data relevant to accountants. It is worth noting that Excel could only load 1,048,576 of 1,584,587 records, leaving 536,011 records unloaded. The data fields used are shown in Exhibit 1; the data itself can be downloaded from the NCCS (https://urbn.is/2IEu5EA).”

http://nccs.urban.org/

Querying and Importing The Dataset

  • Paste URL from website source into the dialog box. Click OK and the query begins.
Access web source via Data >> Get & Transform >> From Web
  • A preliminary view of the data appears. Notice the URL source and then click on “Transform Data” at the bottom.
Preliminary dataset view — click on Transform Data
  • Power Query opens in Edit mode. At this point, I only want to do 2 things — change the name of the query AND notice the “Applied Steps” that have started to be created (at this point, Power Query has done these by itself).
Data view in the Power Query Editor — Load to Data Model
  • Then, we load the data into via the Data Model into Excel. Since there are so many records (1,584,587), the data cannot be loaded directly into Excel. However, contrary to the reference article above, Excel can easily handle more records than rows in a spreadsheet.

By the way, the Data Model handles the data much more effectively and efficiently versus dropping it into an Excel table.

Loading the NCCS Dataset into the Excel Data Model
Data Load Options Review by ExcelIsFun (YouTube)
Query showing all records loaded into the Data Model
Open Data Model — Power Pivot Add-In must be installed to do this
Data Model — Indicates all Records Loaded

Next Steps

Field List of Imported Dataset

This will be explored in the next post — stay tuned!

About Don

“It’s time for DIFFERENT”

Connect with Don!

--

--

Up your Excel game — Tips, tricks and efforts that have exponentially increased my productivity!

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Don Tomoff

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics