Data Transformation And Reshaping With Power Query

Prerequisites

Basic knowledge of Microsoft Excel is essential with the following pre-requisites:

• Basic understanding of Microsoft Excel navigation, including familiarity with the ribbon interface, worksheet navigation, and basic formatting.
• Basic knowledge of common Excel functions such as SUM, AVERAGE, COUNT, and IF.
• Familiarity with importing and exporting data in Excel.
• Access to a laptop or desktop computer with Microsoft Excel installed (preferably Excel 365) for hands-on practice during the training sessions.

Target Audience

For those who need to clean and reshape data (repetitively) in their day-to-day job just to get the data in the right form so that Pivot Table will be able to summarize. Cleaning data includes endless usage of Trim, Clean, and Vlookup functions; reshaping involves tasks like removing columns, clearing up rows, and changing formats. If your job constantly requires such actions, you should join this class.

Learning Outcomes / Benefits

After this training workshop, participants will be able to find and connect to data from a wide variety of sources—from physical files to relational databases and even from the web. They will learn to perform and automate data cleansing and reshaping without having to do the same tedious work repeatedly.

Key Content

Module 1: Welcome On Board

• Get to know Power Query
• What is Power Query
• Installing and enabling Power Query
• Get familiarized with Power Query interface

Module 2: Loading And Connect To Different External Data Source

• Connect and load data from the internet
• Getting data from Excel file
• Getting data from Excel file with multiple worksheets and tables
• Loading data from current workbook
• Extracting data from text files (.txt, .csv, etc)
• Extracting data from relational database

Module 3: Close And Load Query

• Close and load queried data
• Change how data is being loaded
• Enabling and disabling Query pane
• Edit queries

Module 4: Steps Editing

• Edit query steps
• Delete query steps
• Copying steps from Advanced Editor
• Modifying steps in Advanced Editor

Module 5: Column Editing

• Defining data type in each column
• Defining date type using Locale Data Setting
• Enabling and disabling auto detect data type
• Deleting Columns
• Selecting Columns
• Remove Rows
• Keep Rows
• Sorting Columns
• Renaming Headers

Module 6: Data Editing

• Splitting data by delimiter or character count
• Splitting data into multiple rows
• Merging Columns and filling up/down empty cells
• Replace values, change text case, clean and trim data
• Add prefix/suffix, aggregate or transform columns
• Round up or down decimals

Module 7: Table Editing

• Transpose table, promote/demote headers
• Pivot and unpivot columns
• Grouping, aggregate, and extract top 5 in each group

Module 8: Consolidating Queries

• Append multiple queries
• Merging queries with left/right outer or anti joins
• Merging queries with full outer and inner joins

Module 9: Loading And Refreshing Cleansed Data

• Loading transformed data into workbook
• Refresh queries

Methodology

This program will be conducted through interactive lectures, PowerPoint presentations, discussions, and practical exercises.