New Excel - Power Query and Power Pivot

Review of the Excel - Data Cleanup and Power Query course material

Database management basics

• Normal Forms
• Types of relations and their meaning

Advanced Power Query techniques

• Loading data from multiple worksheets
• Loading data from a folder, multiple files at once
• Query optimization options

Loading data tables into the Data Model

Overview of the Power Pivot interface

Creating a Relational Data Model

Date table creation and role

DAX language introduction

Creating counted columns

Accessing values from another table (RELATED function)

Formats

Sorting options

KPI creation and management

Create Power Pivot statements

Create and manage simple (DAX) measures

Basic DAX functions (SUM, AVERAGE, COUNT, DISTINCTCOUNT, DIVIDE, IF, BLANK)

Intermediate Excel skills, such as using complex, nested, conditional, multi-conditional functions.

The course also includes data cleansing, branching and writing multiple nested functions, for which knowledge of the above and/or previous intermediate Excel knowledge, if not essential, is strongly recommended.