Description

Complement to Excel 2013, integrated into Excel 2016, Power Query offers functionality for importing and transforming data from various sources. You will learn how to use this tool to define queries and adapt data to your analysis needs with Excel.

Who is this training for ?

For whom ?

Excel users who need to analyze data from external sources (text files, Access databases, SQL Server, SSAS cubes, etc.).

Prerequisites

Training objectives

Explore Microsoft’s Business Intelligence (BI) offering

Connect to various external data sources

Clean and transform data using Power Query

Work with queries through the graphical interface and get introduced to the M language

Training program

• Introduction to Power Query
  • Explore Microsoft’s BI solutions available within Excel.
  • Understand the data processing chain: Power Query, Power Pivot, and Excel.
  • Learn why and how to use Power Query.
• Import data
  • Explore the “Data / Get & Transform” group in Excel.
  • Create a query and connect to various data sources.
  • Import text and CSV files.
  • Connect to relational databases (Access, SQL Server, etc.).
  • Access OLAP cubes (SSAS).
  • Query data from the Web.
  • Manage data refresh and use the imported data in Excel.
  • Hands-on exercise: Create connections to import text files and load data into Excel.
• Transform data using the Query Editor
  • Sort and filter data.
  • Select relevant rows and columns.
  • Remove duplicates and fix errors.
  • Format text, numbers, and dates.
  • Split columns based on delimiters.
  • Replace specific values.
  • Refresh the data after modifications.
• Advanced Table Management and Manipulation
  • Add new tables.Merge multiple tables.
  • Group rows using statistical functions.
  • Pivot tables (transpose data).
  • Combine data from different sources.
  • Utilize relationships between database tables.
  • Create aggregate tables.
  • Define a data source using an SQL query.
• Creating and Managing Calculated Columns
  • Add new columns.
  • Insert indexes.
  • Create calculated columns.
  • Define columns using formulas.
  • Use arithmetic operators in calculated columns.
  • Fill in missing values
• Advanced Query Editing and Introduction to the M Language
  • Read, understand, and modify queries: introduction to the M language.
  • Edit queries directly in the formula bar.
  • Use the advanced editor for precise editing.
  • Utilize the advanced editor to adjust queries created through the graphical interface.
  • Design calculated columns using the M language.