Using Data Model to Hold More Data

Do you ever run into a situation where you try to pull in data into your Excel via Get Data in the Data tab but there is more data than rows available in your file? I used to use Access for those situations, but lately it looks like Microsoft, maybe since last summer, has revamped its software lineup and deleted the option to purchase Access. Access came in handy when I would download data from SAP, do some data work such as adding in categories for classification, and then load into Access. I was interested in a couple of years’ worth of data and Access can handle that load. Once loaded into Access, I would then use pivot tables to pull in the data that I wanted to look at.

But that appears to be gone now.

Maybe data model is the replacement for Access because it appears to be capable of holding so much more data. Of course, there are some limits to how much you can load into a file holding a data model. I’m looking at this web page that holds some information on limits and it appears that the data model can hold up to almost 2 billion rows.

If you have data that contains way less rows of data than 2 billion, then maybe try pulling the data into the data model.

An example of where I run into the limits of a regular Excel spreadsheet is the coronavirus data from the John Hopkins University: https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_US.csv

I have Excel 2019 so my instructions are based upon 2019. Office 365 may be similar but earlier Excel versions could be different.

So, Image 1 is the screenshot where I’m going to pull in data using Data – From Web route in the Excel menu system and Image 2 is the resulting screen.

Before going into the transform screen or what is more commonly called the Power Query, I went ahead and updated the data by clicking on the icon that is highlighted in yellow in the upper right-hand corner in Image 2.

After clicking on the transform button, I’m directed into the Power Query similar to Image 3. Image 4 is a closer look at the table after making the first row as header. Off to the right you will see some columns with the dates as headers. In order to use pivot tables, it would be preferable to have the dates as a piece of data in a column, headed with a column field header called Date.

Application of Unpivot Columns found under the Transform tab will do that trick for you and you can see the end result in Image 5. I enlarged the image so you can better see the columns.

If you try to load the data at this point, you will run into an error message warning you that the data table has more rows than Excel can handle.

All is not lost. There is a trick to loading that data into a data model, but just know, it looks like you have to first pull in that data from a web site via that method I just went through (Data – From Web). I have tried researching to see if there is a different method for pulling in data from a web site but I haven’t had luck so far.

Once you click okay in the error message, go into the Queries and Connections panel off to the right and right click your table. You will get a box much like Image 7 with a bright yellow box and an option to load the data into a data model. Yep, this is where you will do it.

Once you click on that button, Excel will do the rest of the work for you. Then, to create a pivot table just do Insert – Pivot Table and Excel should automatically check in the pop-up box “Use this workbook’s Data Model”.

If you’ve been using Pivot Tables, the rest is very similar to what you’ve been used to.

One key thing though about dates: earlier I didn’t change the format of the date from text to date format but that needs to be done if you want to use dates as it should be used. If the dates are left in text format, the dates won’t organize in date order – they will be out of order, so always check the date format and reformat them into dates if necessary.