This is 2-Day Data Management with PivotTable, Power Query and Power Pivot 2019 training suitable for people who wish to master in Pivot Table & Pivot Chart for basic excel data analysis and reporting, and then moves to more powerful Power Query and Power Pivot advanced data analysis tools. You will be practicing TWO dashboards’ projects by using Pivot Table and Power Pivot.
Objective:
At the end of the training, participants are able to:
Objective:
At the end of the training, participants are able to:
- Create, format, and customize PivotTables
- Use calculations in PivotTables
- Create Pivot Charts
- Use Power Query to perform data cleaning and transformation
- Build a PowerPivot data model from multiple sources
- Use PivotTable with PowerPivot
- Use DAX functions in PowerPivot
Outline:
-
Chapter 1: Pivot Table Basic
- Create a Table for a Pivot Table report
- Create a Pivot Table from an Excel Table
- Filtering and Sorting Data
- Grouping Data
- Update and Refresh data sources
- Formatting PivotTable
- Changing PivotTable layout
- Convert Pivot Table to Excel Table
-
Chapter 2: Pivot Chart Basic
- Understanding Pivot Chart limitations
- Create a Pivot Chart from a Pivot Table
- Create a Pivot Chart from an Excel Table
- Format a Pivot Chart
- Filter and Sort the Pivot Chart
- Convert Pivot Chart to Excel Chart
-
Chapter 3: Pivot Table Calculations
- Change the PivotTable Summary calculation
- Use % Row or %Column custom calculation
- Use Running Totals custom calculation
- Use % of Total custom calculation
- Use Difference From or % Difference From custom calculation
- Insert a custom calculated field
- Insert a custom calculated item
- Edit & delete a custom calculation
- Chapter 4: Hands-on Create Dashboard with Pivot Table & Pivot Chart Project
-
Chapter 5: Power Query Overview
- Extracting, Transforming and Loading data
- Power Query vs “normal” Excel
- Importing data with Power Query
- Query Editor ribbon, Navigator pane & Preview grid
-
Chapter 6: Data Cleaning & Transformation
- Remove Columns, Remove Other Columns & Choose Columns
- Removing duplicates from a data set
- Replacing values and errors
- Test transformations
- Extract texts before/after delimiter
- Convert texts to uppercase/lowercase
- Split column by delimiters
- Add custom column for computations
- Append queries(combining tables with the same headers
-
Chapter 7: Organizing Power Query Automation
- Best practices in organizing the data sources
- How to automate data refresh
- Importing multiples located in a folder
- Update Power Query to change the location of data source
- Change the “Close & Load to” options
-
Chapter 8: PowerPivot Basic
- Standard Pivot Table or Data Model Pivot Table?
- Excel Power Pivot & Power BI Desktop?
- Explore the PowerPivot Application
- Import Data from Various Data Sources
- Refresh Data from a Data Source
- Create Linked Tables
-
Chapter 9: Working with PowerPivot Data
- Organize and Format Tables
- Create Calculated Columns
- Sort and Filter PowerPivot Data
- Create and Manage Table Relationships
-
Chapter 10: Building PowerPivot Reports
- Build a PivotTable
- Build PivotCharts
- Filter Data Using Slicers
- Present PivotTable Data Visually
-
Chapter 11: Using DAX Functions in PowerPivot
- Introduction to DAX Formulas: Measures & Calculated Columns
- Understand the difference between calculated columns and measures
- Understand the difference between implicit and explicit measures
- DAX calculated column using DAX Functions Related and Round
- Chapter 12: Hands-on Create Dashboard with Power Pivot, Power Query and Dax Formulas Project
Duration:
2 days (14 hours)
Target Audience:
This course is highly recommended for advanced excel users, data analysts, financial analysts, IT specialist, reporting professionals, and anyone who needs to fetch data from different sources and transform it in Excel for further analysis.
Methodology:
Demonstration, practical learning, case discussion and exercises
Prerequisite(s) or equivalent knowledge:
Basic knowledge of Excel and using simple Microsoft Excel formulas and functions.
Methodology:
Demonstration, practical learning, case discussion and exercises
Prerequisite(s) or equivalent knowledge:
Basic knowledge of Excel and using simple Microsoft Excel formulas and functions.
US 15214 
GB 12486 
CA 10311 
AU 9183 
IE 5519 
BR 3125 
CN 1425 
NZ 1291 
