Course Details
Course Outline
5 days course
Key functions to prepare data for pivot table reporting
- Table format
- Lookup functions
- Text functions
- Naming cells
Advanced techniques in creating and customizing pivot tables
- Number and cell format
- Report layout
- Calculation in value field
- Grouping and un-grouping fields
Default and customized sorting and filtering
- Sorting using custom list
- Creating calculated field
- Filtering using slicers and timelines
- Connecting multiple pivot tables to one set of slicers
- Customizing reports using the GetPivotData option
Power Query: A must-have skill
- Introduction to this new feature
- Where does Power Query fit in the Power family!
- Get and transform: Link your Excel to external other data sources
- Excel files
- Text files
- Web
- SQL
- Creating and editing the Query
- Get data from: Tables, files and folders
- Power Query to clean up data
- Practical examples:
- UnPivotting data
- Working with nested column headers and merged cells
- Naming, merging, splitting and removing columns
- Filtering rows in different ways
- Transforming and formatting data
- Combining queries: Merge and Append
- The different types of joining data
Analyzing disparate data sources with pivot tables
- Utilizing pivot table wizard
- Using internal data model
- Building pivot tables using external data sources
The new world of PowerPivot
- Benefits and drawbacks of PowerPivot
- Merging data from multiple tables without using Vlookup
- Creating better calculations using the DAX Formulas
- Using DAX to create calculated fields
- Calculate and Related functions
