Become an Excel Expert using the Power Platform Supercharge your Excel skills and learn multiple ways to boost productivity! 

Learn how to work with large data sources, transform & clean your data using Power Query and create an advanced data model in Power Pivot. Take advantage of Microsoft Excel’s cloud automation, Office Scripts and use the Power Platform to further automate your spreadsheet both in the cloud and on your desktop. You will also be introduced to Power BI and see how quickly you can turn your data into actionable insights with just a few clicks.

Introduction

• Course Overview

Open Power Query

• Open Power Query
• Configure Power Query
• Add Power Pivot
• Modify the Quick Access Toolbar
• Configure Power Query

Get Data

• Import Data from
• CSV File
• Excel Table, List, Pivoted data
• Folder
• PDF File
• Type in the Data
• Load the Data to Excel

Transform Data

• Applied Steps
• Deleting Applied Steps
• Adding Applied Steps
• Editing Data using
• Fill Down
• Trim
• Replace Values
• Filters
• Modify the filter manually
• Format
• Removing Errors
• Unpivoting Data

Working with Columns

• Deleting Columns
• Renaming Columns
• Adding Columns
• Standard & Custom Date Columns
• Renaming Applied Steps
• Create a Conditional Column

Working with Queries

• Grouping Queries: Creating Query Groups
• Appending Queries
• Append Queries
• View the loaded Queries
• Rename Queries
• Reloading the Queries
• View the re-loaded Queries
• Stop a single data load
• Stop multiple data loads
• Extended exercise - Macro

Power Pivot

• Data Model
• Add Tables to a Data Model
• Arrange the Data Model
• Connect the Data Model using Create
• Connect the Data Model using Manage
• Connect the Data Model using Click & Drag
• Pivot Table
• Pivot Table from Excel
• Pivot Table from Power Pivot
• Measure in Power Pivot
• Sum in DAX
• Pivot table for Measures
• Measure with Filter

Data Types

• Standard Data Types
• Stock Data Type
• Uploading Stock Data Type
• Displaying Extra Data
• Custom Data Type - Get Data
• Custom Data Type - Transform Department Data Custom
• Data Type - Transform Countries Data
• Custom Data Type - Merge Data
• Create Custom Data Type
• Create Custom Report
• Extend the Custom Report

Private and Group Classes:
This course is available for individual enrolment and Private classes (min 10 students). Please contact Lumify to organise a quote.

What do I need to know to attend?

This course is aimed at experienced Excel users who want to learn more about handling data in Power Query and building automations into their models.

Excel Expert Learning Outcomes

You will learn how to:

• Work effectively with large datasets in Excel
• Transform and Clean your data in Power Query
• Create data relationships
• Create customised calculations in Power Pivot
• Use Office Scripts for automation
• Create workflows with Power Automate
• Create visualisations of your model in Power BI
• How to connect Excel and Power BI data
• Understand when to choose Excel or Power BI

Excel Advanced or relevant experience