Use a Standard Pivot Table when…

• You have one Excel table
• Standard calculations offered by the Pivot Table are enough

Use the Data Model Power Pivot when…

• You have two or more related tables
• You’ll use the same KPI’s in multiple reports (sales, quantity etc.) You can take advantage of embedded number formatting.
• Have large data
• Need a variety of calculations (Time related calculations, periodic versus YTD, distinct count, new customers)

For professional models: Load your data via Power Query to the data model.

This way you can easily update the source and clean the data before you create your pivot tables.

Don’t forget about Power Query’s strengths

•  You can upload data from different sources
•  You can append the data (for examples sales data from different stores)
•  You get to keep your source data separate to your report
•  You can clean the data, remove columns you don’t need and add new ones
•  If you just need a standard Pivot Table, you can merge data and create one Pivot Table. 
•  You can load data beyond the capacity of Excel (more than 1 million rows)