Process and model data to derive actionable information
All organizations have data, sometimes very large. They are valuable sources of information when you are able to leverage them. Excel's PowerQuery and Power Pivot add-ins can help you make the most of your data to make informed and objective decisions. Reading data is also the door to unexpected findings that will improve efficiency or performance.
During the training you will see how to import and process data with Power Query, how to model it with Power Pivot and how to analyze it with Excel pivot tables.
Is it for you ?
This course is designed for professionals who want to develop data analysis models in management and who want to get the most out of Excel using the Power Query and Power Pivot add-ins.
Prerequisites
Knowing pivot tables
What You'll Walk Away With
- ✓ Transform and clean data efficiently using Power Query (merge, calculated columns, unpivot)
- ✓ Combine and enrich multiple data sources to build usable datasets
- ✓ Model relational data with Power Pivot using fact and dimension tables
- ✓ Create business metrics with DAX (measures and calculated columns)
- ✓ Analyze data using pivot tables to generate actionable business insights
Training content
1 Power Query Editor:
Query and data processing editor (Connect to data, Transform data and load processed data into Excel and/or Power Pivot).
- Merge columns
- Add custom columns (calculated), columns from examples, conditional columns (if functions), etc.
- Sort data, Extract characters from a sequence, Shift, lowercase, etc.
- Replace values, rounding, statistics, etc.
- Depivot columns
- Combine queries
- Add queries
2 Power Pivot editor:
Modeling tool (DAX language)
- Power Pivot Diagram View: Creating relationships (cardinalities) between data tables (dimension tables and fact tables)
- Power Pivot data view: Modeling with the DAX language: Introduction to the DAX language (Data Analysis eXpression) ; Create calculated columns (Data View) ; Creating measures (Data View)
3 Power Pivot Tables
Keep in Mind
This training will not cover the M language of Power Query
📌 Practical information
Our training sessions are offered in Montreal or Quebec City, in person or in a virtual classroom. Dates and locations are specified when you select your session below. If you have any questions, check out our FAQ.
