Below is a link to the downloadable Excel file that contains a sheet with the table we will be using in this series of posts. Feel free to download it. It’s called TimyIMDB.xlxs. I am using Microsoft 365. You can use the file to practice loading the tables into the data model.

TinyIMDB.xlxs

Click the above link to download the Excel file.

1. Click inside the tbltitlebasics table
2. Select the Data tab, From Table/Range
3. In Power Query perform any Transformations needed (see below)
4. Close & Load to…,
5. Only Create Connection and check Add this data to the Data Model
6. OK button

Transformations for tbltitlebasics

1. Click on the endYear column by clicking on endYear
2. Transform tab
3. Replace Values, Replace Values
4. Find \N
5. Replace With null
6. Ok button
7. Click on the ABC123 button of endYear
8. Change the data type to Whole Number
9. Check that everything else is good (query name, data types etc)
10. Click on the Home tab, Close & Load To…

For each of the tables in Tiny, you would perform similar steps. Some tables here don’t require any transformations at all because I have made them so simple and small. The actual downloadable IMDB files often require many transformations to clean them up.

In Excel, click the Data tab (menu). Click Queries and Connections. Hover your mouse over the first and only query listed to see the popup window displayed in the screenshot below.