Power Query and Power Pivot in Microsoft Excel

Businesses in the digital age rely on data as leverage to assess risks, determine market trends, make investment decisions and formulate expansion strategies. Power Query, Power Pivot and Microsoft Excel offer three tools that have revolutionised data modelling and management technology. Power Query enables us to perform data extraction, transformation and loading (ETL) operations. Likewise, Power Pivot is an MS Excel package used to import data, create data models and perform data analysis.

This data analysis course demonstrates how to apply the Power Query and Power Pivot tools for simple data manipulation in MS Excel. First, we highlight the vital functions of Power Query in data analysis, including its application in the automation of routine tasks and the reshaping of data. We then present a real-time demonstration of the Power Query tool for file extraction, data formatting and file consolidation.

The course goes through the basic steps for unpivoting data with the Power Query tool. We then examine the Power Pivot tool and demonstrate how it works, including how its data model feature displays processed data in a pivot table. We explain how to create custom calculations (measures) and determine performance measures through the key performance indicator (KPI) functionality of the Power Pivot tool. Finally, we outline some key Data Analysis Expressions (DAX) functions used in Power Pivot. Enrol in this data science course to learn how to use these powerful tools to analyse and manipulate data.