Duration
2 days
Overview
If you are ready to take your Excel skills to new heights and game to change the way you use Excel for data analysis, this course is for you. This course introduces Excel’s powerful data modelling and business intelligence tools: Power Query and Power Pivot.
Excel Power Query is one of the most powerful features and the easiest to learn & use within Microsoft Excel You can use Power Query to consolidate several files with the same format, clean & transform your data that you download from your company’s database system and display it in a report for Excel to work with. Power Query a great alternative to Excel VBA as it enables you to process your tables simply by clicking on buttons without coding, visualize the operations step by step without running a single macro and edit the steps order of different steps easily through dragging-and-dropping.
Excel Power Pivot is often associated with Power Query to ease data analysis and time-related calculations. Power Pivot enables you to analyse databases of several million lines by extracting aggregated information according to the rules set by your business. It is simply the next step of data transformation, and it allows users to create data models from tables previously cleaned by Power Query.
Learning Outcomes
This workshop aims to equip you with the skills to set Excel’s data modelling and business intelligence tools use Power Query and Power Pivot
- Use Power Query to clean the data by replacing values and filtering the data
- Use Power Query to reshape the data by removing all columns
- Use Power Query to Import Multiple CSV Files
- Use Power Query to merge 3 Tables into One in PowerPivot
- Use Power Pivot to create PivotTables and PivotCharts
- Use Power Pivot to create DAX functions and KPI
- Use Power Pivot to import and create relationships between tables
- Use Power Pivot to create an interactive Dashboard
Who Should Attend?
- Microsoft Excel 2016 / 365 / 2019 users who do not have experience in using Power Query and Power Pivot.
- Participants must be frequent users of PivotTable and proficient in PivotTable and PivotChart.
- Users who need to work with more than 1,048,576 rows of data (maximum rows in Excel worksheet) and want to go beyond the limits.
Note:
This workshop is only for Windows version Microsoft Excel users. Participants are required to bring a laptop equipped with Windows version Microsoft Excel 2016/ 2019/ 365 version.
Course Outline
Exploring Excel 2016/2019/365 Power Query
- Introducing Excel Power Query
- Using Power Query to import CSV files
- Using Power Query to Append Tables
- Using Power Query to Merge Tables
- Using Power Query to Unpivot Data
- Using Power Query to Group Records by Fields
Exploring Excel Power Pivot
- Introducing Excel Power Pivot
- Getting Started with Power Pivot
- Using Linked Excel Tables in Power Pivot
- Importing Data from Text Files
- Creating PivotTables and PivotCharts
Creating Data Model, PivotTable and PivotChart
- Understanding Power Pivot Data Model
- Importing Data from multiple Excel Tables
- Creating Relationships in Diagram View
- Creating PivotTable from Power Pivot Data
- Creating PivotChart from Power Pivot Data
- Formatting PivotTable and PivotChart
Creating Calculations in Power Pivot
- Understanding DAX Functions
- Using DAX in Calculated Columns
- Creating Measures in Power Pivot
- Creating KPIs to use in PivotTables
Building a Dashboard with Power Pivot
- Introduction to Dashboard Reporting
- Arranging the Right Chart Visualizations
- Adding Slicer to Dashboard
- Adding Timeline to Dashboard
- Analysing Data from Dashboard Reports
Programme scope, delivery arrangements and suitability are confirmed during enquiry.