DAY 1 Tables and PivotTables
Data principles
Tables
- Create tables
- Layout
- Sort and filter data
PivotTables
- Create PivotTables
- Group
- Calculations
- Filtering (incl slicers)
Presentation
DAY 2 Power Query
Power Query introduction
- What is the role of Power Query
- How Power Query works (using a detailed example)
Data connections
- Importing Excel files
- Import CSV and TXT files
Export
- Export to Excel
- Export to the Data Model
- Adjust load location afterwards
- Adjust Default Load Location
Transformations
- Column from examples
- Conditional columns
- Date transformations
- Splitting columns
Merge tables
- Merge 2 separate lists
- Reading the folder
- Optional: sheets from 1 file
Expand tables
- Linking 2 queries together (VLOOKUP like)
Create date tables via M-code
- Importing a date table via a step-by-step plan
DAY 3 Power Pivot
Recap Day 2
- A quick recap of Power Query
Data model
- Why modeling
- How to Create Relationships
- Multiple relationships between 2 tables
- Filter direction
Getting Started with DAX
- What is DAX and how do it differ from regular Excel formulas?
- Calculated columns vs Measures
- Always refer to the Table in which a column is located
- Writing tips
DAX - Calculated columns
- How to make a calculated column
- YEAR(... ) MONTH( ...) Line Total
- RELATED and LOOKUPVALUE
- RELATEDTABLE
- Optional: If-then-else calculations
DAX - Measurements
- Where to make measures
- In which table?
- Creating a measure table is an option
- Create PivotTables
BASIC Aggregations
- What are basic aggregations?
- SUM, COUNTROWS, DISTINCTOUNT
- Create PivotTable
- Optional: Average and STDEV
Aggregating Expressions
- What Are Aggregating Expressions
- Apply SUMX
- FILTER + COUNTROWS
- FILTER + SUMX
- AVERAGEX + FILTER + BLANK
CALCULATE
- DEMO: What makes CALCULATE so important
- CALCULATE sales compared to Belgium
- CALCULATE compared to Grand Total: with ALL
- CALCULATE compared to SUBTotal: with ALLEXCEPT
- CALCULATE when there are multiple relationships, USERELATIONSHIP
DAY 4 More DAX
Summary Day 2 and 3
Date intelligence
- What is Date Intelligence
- Why Do We Need a Date Table
- Adding a Date Table
Date intelligence functions
- TOTALYTD
- Demo DATESYTD (Why is this CALCULATE)
- DATESBETWEEN for a Total Quantity To Date
- SAMEPERIODLASTYEAR
- DATEADD
- Delta calculations: ThisYear - PreviousYear
- Growth calculations: (ThisYear - PreviousYear) / PreviousYear
Corporate identity and institutions
- What are themes?
- Creating and Applying Themes
- Create and apply cell styles
- Hide worksheets
- Protect Worksheet and Workbook
- Object naming convention
