Excel Video 475 Loading Data into the Excel Data Model

Loading data into the Excel Data Model is the first step to taking advantage of the power of this new tool, and that’s the subject of Excel Video 475. Excel has a new tool called Power Query that can load data into the Excel Data Model and transform the data to meet requirements you may have. We’ll discuss Power Query in future Excel Videos. Today I’ll introduce three of the most common ways to get data into the Excel Data Model through PowerPivot that should at least get you started.

One way to add data is to link an Excel table to the Excel Data Model. Note that linking data means that if the data in the Excel table changes the data in the Excel Data Model can refresh.

A very common data source for medical practices is SQL Server since many PM and EHR systems store data in SQL Server. Note that as I show you how to link to SQL Server data, you need to know the name of the server, have appropriate login rights to read the data (either using Windows Authentication or SQL Authentication), and know the names of the table(s) you want to import. You might need IT help to obtain these credentials. I create these SQL Server tables for my clients all the time. This data can also automatically refresh as the data in your PM or EHR changes.

The third way to import data is to copy and paste the data into the Excel Data Model. The advantage of this method is how simple it is. Simply copy data from a source that stores the data in rows and columns and paste it. Provide a table name and your data is ready to go. The disadvantage of copy/paste is that pasted data can’t be refreshed as the data changes.

We’ll talk about how to refresh data in the next Excel Video and then we’ll create relationships between our tables. I look forward to seeing you then.