Excel Power Query: Automation in Data Transformation

Requirements:

• Bring your Windows laptop
• Must have MS Excel installed. Version 2016 / 2019 / 2021 / Microsoft 365
• If you don’t have it yet subscribe and install it from www.office.com
• Must have basic knowledge in Excel PivotTable

Inclusions:

• Light snacks and meal
• Softcopies of Handouts and Excel templates
• Certificate

Objective:

• Combine large and multiple files
• Automate data cleaning and preparation
• Converting data types to properly group data
• Managing multiple queries
• Use M functions to customize data transformation
• Compare different datasets

Topics

• Understanding ETL
• Introduction to Power Query
• Understanding Interface of PQ Editor
• Different ways to import data
• Updating source data
• Working with data type
• Data cleaning and formatting
• Format extract split to columns filter sorting etc.
• Calculating numbers and fields
• Aggregate rounding etc.
• Date and Time transformations
• Working with tables
• Transposing unpivot removing duplicates indexing etc.
• Managing Queries
• Working with Add Columns
• Custom columns conditional columns 
• Manipulate M formula
• Working with Join Kind in Merging Queries
• Solutions of Power Query from common to complex data transformation

Host or Publisher JTR Services PH