“Microsoft Excel Power Query” is a comprehensive course designed to equip participants with the skills and knowledge to effectively utilize Power Query, a powerful data transformation and analysis tool in Excel. This course covers a wide range of Power Query functionalities, enabling participants to efficiently extract, transform, and load data for analysis and reporting purposes. Participants will begin with an introduction to Power Query, exploring its features and compatibility with different Excel versions. They will learn how to pivot columns, split columns into multiple columns, filter and sort rows, and transform and add columns to meet specific data requirements.
The course delves into the intricacies of joining and merging data using Power Query, including full outer joins and right anti joins. Participants will also discover how to convert reports into pivot tables, unpivot columns, and extract data from multiple worksheets for comprehensive analysis. Power Query’s capabilities extend to working with diverse data formats. Participants will learn how to trim data, format dates and values, parse URLs, split text fields, and perform grouping operations for aggregating data.
In addition, the course explores the M language, the underlying language of Power Query. Participants will gain insights into M functions, passing functions, invoking functions, working with variables, and utilizing simple expressions for efficient data manipulation. Throughout the course, participants will have access to exercise files, workbooks, and additional resources to reinforce their learning experience. “Microsoft Excel Power Query” is an essential course for data analysts, Excel users, and professionals who work extensively with data. By mastering Power Query, participants will streamline their data transformation processes, enhance data analysis capabilities, and gain a competitive edge in their data-driven roles.
What Will You Learn?
- The fundamentals of Power Query and its compatibility with different Excel versions.
- How to pivot columns, split columns into multiple columns, filter and sort rows, and transform and add columns using Power Query.
- Techniques for joining and merging data, including full outer joins and right anti joins.
- How to convert reports into pivot tables for comprehensive data analysis.
- Methods for unpivoting columns and extracting data from multiple worksheets.
- Data manipulation techniques such as trimming data, formatting dates and values, parsing URLs, and splitting text fields.
- The basics of the M language, including finding the list of M functions and passing functions.
- How to work with variables and utilize simple expressions for efficient data manipulation.
- Enabling M in Power Query and using M functions effectively.
- Access to exercise files, workbooks, and additional resources to reinforce learning.
Who Should Take The Course?
- Data analysts and data professionals who work extensively with data in Excel.
- Excel users who want to enhance their data manipulation and analysis capabilities.
- Professionals who regularly work with large datasets and need efficient methods for data extraction and transformation.
- Business intelligence professionals seeking to optimize their data analysis processes using Power Query.
- Data-driven professionals in various industries who want to leverage Excel’s Power Query for improved data insights.
- Excel users who want to learn advanced data manipulation techniques and maximize the functionality of Power Query.
- Individuals involved in reporting, data cleansing, and data transformation tasks.
- Professionals looking to streamline their data analysis workflows and save time on repetitive data tasks.
- Anyone interested in learning about Power Query and its applications for data manipulation and analysis in Excel.
Course Features
- Lectures 40
- Quiz 0
- Duration 3h 20m
- Skill level All levels
- Language English
- Students 28
- Certificate Yes
- Assessments Yes
Curriculum
- 5 Sections
- 40 Lessons
- 1100 Days
- Power Query24
- 2.1Power Query Intro and Excel version3 Minutes
- 2.1Power Query and Query Editor Ribbon9 Minutes
- 2.1Trim5 Minutes
- 2.1Format Dates and Values2 Minutes
- 2.1Parsing URLs5 Minutes
- 2.1Split Text Fields10 Minutes
- 2.1Group By3 Minutes
- 2.1Import From Folder8 Minutes
- 2.1Doing Auto Cleanup8 Minutes
- 2.1Extract Data from Forms13 Minutes
- 2.1Extract – Multiple Criteria5 Minutes
- 2.1Extract Multiple Worksheets4 Minutes
- 2.1Unpivoting Columns5 Minutes
- 2.1Pivoting Columns2 Minutes
- 2.1Split Columns into Other Columns4 Minutes
- 2.1Filtering Rows5 Minutes
- 2.1Sorting Columns2 Minutes
- 2.1Transform and Add Columns7 Minutes
- 2.1Intro to Joins4 Minutes
- 2.1Merging8 Minutes
- 2.1Full Outer Join6 Minutes
- 2.1Right Anti Join9 Minutes
- 2.1Convert Reports into Pivot Tables5 Minutes
- 2.1Modulo6 Minutes
- M Language12
- 3.1M Introduction3 Minutes
- 3.1Enabling M in Power Query2 Minutes
- 3.1Simple Expressions8 Minutes
- 3.1Simple Expressions – Nested Expressions3 Minutes
- 3.1Variables8 Minutes
- 3.1Functions6 Minutes
- 3.1Functions – Reusable Functions2 Minutes
- 3.1Functions – Invoking1 Minute
- 3.1Passing Functions9 Minutes
- 3.1Passing Functions – keyword each3 Minutes
- 3.1Finding the list of M functions2 Minutes
- 3.1Using M Functions10 Minutes
- Resources2
- Resource1
- Premium Certificate & Transcript1
