Description

Power Query is a technology that allows you to find, link, merge and optimize your data sources for analysis. Power Query will be able to import, clean and evaluate millions of rows in the data model. Compared with other Excel tools it is an incredibly short learning curve. You set up a query once and use it again with a simple refresh. Power query helps to prepare your data for a pivot table report. A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data

Learning Outcome and Goals

• Understand Excel Data thoroughly & prevent common mistake in Excel Reports
• Manage & Analyze Database/Excel List effectively
• Ability to connect data from another source/file
• Introduction to Macro Recording to automate repetitive task
• Manage worksheet & file Protection

Course Requirement

• Participants should be able to use a PC at the beginner level
• Basic knowledge and functionality of Microsoft Excel
• Microsoft Office 2016 and above

Course Outline

Introduction to Power Query

• Exploring Power Query User Interface
• Excel Get & Transform Group
• Power Query Basics
• The Query Editor
• Understanding Query steps
• Refreshing Power Query data
• Managing Existing Queries
• Understanding Data Destinations
• Close & Load
• Close & Load To…

Power Query Data Sources

• Data Sources Overview
• Power Query Data Sources
• Get Data from
• CSV and Text Files
• Current Excel worksheet
• Excel Workbooks
• Folder
• Database

Transform Data – Columns

• Transform data Overview
• Working with Columns
• Creating Custom Columns
• Pivot Column
• Unpivot columns

Transform Data – Rows

• Filtering Rows
• Filter a column using Text Filters
• Filter a column using Number or Date/Time Filters
• Filter a column by Row Position
• Keep Top Rows
• Keep Top 100 Rows
• Keep Range of Rows
• Remove Top Rows
• Removing Duplicate Records
• Remove rows with errors

Manage Data from External Source

• Load Data as Connection only
• Append Query
• Load to PivotTable
• Understanding PivotTable Cache Memory
• Manage and secure your data
• Avoid exposing your data source

Creating Pivot Table

• Designing a PivotTable
• Adding Elements to the Report
• Use Slicers to Filter Report
  • What is Slicers?
  • Remove Slicer
• Update the Data Source
  • Changes Data Sources
  • Create a dynamic Range for the Data Table

Use “Fields, Item and Sets”

• Creating Calculated Field
• Creating Calculated Item
• Edit and Delete Calculated Field or Item

Pivot Charts

• Convert PivotTable to PivotChart
• Manage Chart Elements
• Format PivotChart
• Using Slicer to Filter Chart

PivotTable Wizards

• Multiple Consolidation Ranges
  • From Multiple Worksheet
  • From Multiple File
  • Column, Row and Pages
• Manage PivotTable fields
• Update new data