Next we’ll use Power Query to bring in the ratings data into the Data Model.

There are no transformations to do here with tbltitleratings. This table is very simple. There is a one-to-one relationship between this table and the titles table. We will reduce the number of titles to only include those titles that have ratings. If a title doesn’t have a rating, we will ignore it and actually eliminate it from our data model. The original IMDB database is really too large for the average computer to work well with.

First of all, one-to-one relationships should be removed from the data model because they are not efficient. We can convert two tables into one with Merge.

Merge

Once we have the tbltitlebasics and tbltitleratings tables loaded into the data model, we can merge them together and create a new table called tbltitles. I am using an inner join.

In Excel, go to the Data tab, Get Data, Combine Queries, Merge.

In the Power Query Editor we see something like the following screenshot when we look near the last two columns. Rename it to tbltitles. Click the expand button.

Close and Load To…, Only Create Connection and load it to the Data Model.