Course Overview
TOPThis course familiarizes the participant with Microsoft Power Query, a revolutionary data tool for Excel that allows you to intuitively discover and automate the import, transformation, and combination of data across a variety of data sources for use in Excel and Microsoft Power BI. Power Query is a free add-in for Excel that enhances the self-service Business Intelligence experience in Excel. The course uses Excel 2016, however Power Query works with Excel 2010 and above.
Scheduled Classes
TOPWhat You'll Learn
TOP- Understand the revolutionary changes in Power Query and the ease it provides for Excel pros
- Successfully navigate the Power Query interface
- Understand and properly configure data types
- Understand and perform importing data
- Understand and perform transforming data
- Understand and perform loading data
- Understand and utilize ranges in Excel
- Understand and handle special situations
- Understand and perform appending operations
- Understand and utilize working with folders and files
- Understand and perform combining worksheets
- Understand and utilize PivotTables
- Understand working with relational data
- Understand and perform importing from SQL Server Analysis Services Multidimensional
- Understand and perform importing from SQL Server Analysis Services Tabular
- Understand the issues encountered when performing merges
- Understand and utilize merges
- Understand and utilize loading into Excel
- Understand and utilize loading into Power Pivot
- Understand and utilize loading to Power BI Desktop
- Understand and utilize the data sources available
- Understand and utilize grouping
- Understand and utilize summarizing
- Understand the M interface
- Understand Power Query formulas
Outline
TOP
Viewing outline for:
- Introduction to Power Query
- Lab : Introduction to Power Query
- Power Query Interface
- Understanding Data Types
- Lab : Introduction to Power Query
- Understanding The Ease That the Revolutionary Changes Afforded by Power Query Provide to the Excel Pro
- Examining The Power Query Interface
- Configuring Data Types
- Working with CSV, TXT and Excel Worksheets
- Importing Data
- Transforming Data
- Loading Data
- Using Ranges in Excel
- Understanding and Handling Special Situations
- Appending Operations
- Working with Folders and Files
- Combining Worksheets
- Using PivotTables
- Lab : Working with CSV, TXT and Excel Worksheets
- Importing, Transforming, and Loading Data
- Utilizing Ranges
- Understanding and Handling Special Situations
- Appending Operations
- Working with Folders and Files
- Combining Worksheets
- Using PivotTables
- Connecting Databases
- Working with Relational Data
- Importing from SQL Server Analysis Services Multidimensional and Tabular
- Lab : Working with Databases
- Importing From SSAS Multidimensional
- Importing From SSAS Tabular
- Performing Merges
- Understanding the Issues
- Performing Merges
- Lab : Performing Merges
- Loading your Data
- Loading into Excel
- Loading into Power Pivot
- Loading into Power BI Desktop
- Lab : Loading Your Data
- Importing, Transforming, and Loading Data into Excel and Power Pivot
- Introduction to Power BI Desktop
- Data Sources
- Grouping and Summarizing with Power Query
- Grouping Options
- Summarizing
- Lab : Grouping and Summarizing with Power Query
- Working with the Power Query Language “M”
- Understanding the “M” Interface
- Examining Power Query Formulas
- Lab : Working with the Power Query Language “M”
Prerequisites
TOP- Required: Should be familiar with Excel and the concepts of datasets and basic reporting, including the use of PivotTables
- Recommended: Excel Part 1
- Recommended: Excel Part 2
Who Should Attend
TOPBusiness Intelligence Professionals