Microsoft Excel: Power Query and Power Pivot for Beginners
Learn how to use Power Query extract and transform data, plus learn how to use Power Pivot to explore and analyze the data model
Take Your Excel Skills to the Next Level
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 modeling 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.
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 wanting to go beyond the limits.
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
“Trainer was patient in listening and explaining to all queries. Trainer provided detailed explanation. The course will be helpful in data entry and formulating.”
Nicole Wong, Brainy Moves Pte Ltd
“Valene made learning Excel enjoyable. Lessons learnt are commonly used at work.”
Chan Yoke Fan, Singapore Art Museum
“Trainer is very friendly and helpful. Course is very informative”
Muhd Abdul Hadi Mansor, Muslim Converts’ Association of Singapore
MS Excel Expert and Microsoft Certified Trainer: Valene Ang
Valene Ang is a Microsoft Certified Trainer and Master Instructor of Microsoft Office Specialist (MOS). She brings with her 20 years’ experience in corporate training and consultancy. Valene has a broad experience in customizing Microsoft Office training programs, developing customized course outline and course material, assisting corporate clients in business data analysis and providing dynamic report solutions.
She had conducted many Microsoft Office workshops in Singapore and Malaysia. Her training focuses on providing practical solutions to real life Excel problems. As a Certified Trainer, Ms Ang has trained hundreds of executives, managers and directors from across medium sized organisations, government offices and multinational companies. Some of her high profile clients including PSA International, DFS Galleria Singapore, Neptune Orient Lines Limited (NOL), National Environment Agency (NEA), Inland Revenue Authority of Singapore (IRAS), CPF Board, Public Utility Board (PUB), Canadian High Commission, British High Commission, Temasek Polytechnic, Republic Polytechnic, Baxter Healthcare Pte. Ltd, and many more.
Exploring Excel 2016 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 2016 Power Pivot
- Introducing Excel Power Pivot
- Getting Started with Power Pivot
- Using Linked Excel Tables in Power Pivot
- Importing Data from Text Files
- 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
- Understanding Power Pivot Data Model
- Importing Data from multiple Excel Tables
- Creating Relationships in Diagram View
- Creating PivotTables and PivotCharts
- Adding Slicer to Dashboard
- Adding Timeline to Dashboard