It’s always very helpful to have a database to work with when you are learning software such as Excel, Power Query, and Power Pivot. Fortunately, there are several sources of free, and “fake”, data on the Internet that you can download. The FoodMart database is fictional supermarket chain. This is a good database to start with because it is not too complicated and not large.
This database is a sales database. It has transactional data. It has sales transactions and returns. After doing some work with this database, following along with Chris Dutton’s Maven Analytics course at Udemy.com that’s called Microsoft Excel: Business Intelligence w/ Power Query & DAX, I have produced the following screenshot of the Diagram View in Power Pivot.
You can see that there are a lot of lookup tables here, and only one data table. Data tables are also known as “fact” tables, and lookup tables are also known as “dimension” tables. The lookup tables have primary keys and the data tables have foreign keys.
The diagram below includes the Returns table and some measures that I created, as well as a few comments.
Consider this best practice from Chris Dutton: “Data tables should ideally only contain quantitative values and foreign keys; any extra descriptive columns should live in a related lookup table.” and “Eliminate redundant columns; keep data tables narrow”. A transactions table shouldn’t have a bunch of product information in it. The product ID and perhaps the price should be in a transaction (or “Orders”) table. Don’t include things like the product description in a transactions table when you could have put that in the Products table.