Description
Mynda Treacy – Power Query for Excel Course
Overview
If you import or copy/paste data into Excel, you can probably automate many of the related manual tasks using Power Query. Traditionally, we spend a lot of time in Excel simply preparing data for use, for example, getting it ready for a PivotTable, formula-based report, or upload. Then, we get to repeat those same manual steps next month, and the next. Power Query changes all of that because once we define the steps needed to prepare the data, it becomes a simple refresh next period … and every subsequent period :-)
Outline
-
- Lesson 1: Introduction
- Lesson 2: Data Sources
- Lesson 3: Combining Multiple Sources
- Lesson 4: Transforming and Cleaning Data
- Lesson 5: Data Destinations and Refreshing Queries
- Lesson 6: M Language
- Lesson 7: Parameter Tables and Custom Functions
- Lesson 1: Introduction
- 1.01 What is Power Query (3:00)
- 1.02 Installing Power Query (2:24)
- 1.03 Interface (4:31)
- Exercises, Quiz, and Additional Resources
- Lesson 2: Data Sources
- 2.01 From Web (6:35)
- 2.02 From Excel File (4:07)
- 2.03 From Current Workbook (4:07)
- 2.04 From CSV or Text File (2:48)
- 2.05 From Folder (11:41)
- 2.06 From Database (SQL) (6:54)
- 2.07 From Other Sources (OData feed) (2:13)
- 2.08 Online Search/Data Catalog Search (5:23)
- Exercises, Quiz, and Additional Resources
- Lesson 3: Combining Multiple Sources
- 3.01 Tables or Named Ranges (3:33)
- 3.02 Worksheet data (7:01)
- 3.03 Appending Queries (7:24)
- 3.04 Merging Queries (15:18)
- 3.05 Query Dependencies View (4:03)
- Exercises, Quiz, and Additional Resources
- Lesson 4: Transforming and Cleaning Data
- 4.01 Editing Queries (2:47)
- 4.02 Data Types (3:17)
- 4.03 Fixing Dates with Locale (4:46)
- 4.04 Columns (2:35)
- 4.05 Splitting Columns (9:09)
- 4.06 Merging Columns (1:25)
- 4.07 Filtering Rows (4:56)
- 4.08 Remove Duplicates and Errors (3:24)
- 4.09 Sorting (1:57)
- 4.10 Replace and Fill (1:59)
- 4.11 Text and Numbers (3:20)
- 4.12 Transforming Dates (5:35)
- 4.13 Custom and Index Columns (7:44)
- 4.14 Custom Calculated Columns (4:25)
- 4.15 Custom Date and Time Columns (5:50)
- 4.16 Grouping (3:14)
- 4.17 Unpivot (3:34)
- 4.18 Transpose (2:54)
- 4.19 Duplicate and Reference (3:53)
- 4.20 Add Column from Examples (5:50)
- Exercises, Quiz, and Additional Resources
|
- Lesson 5: Data Destinations
- 5.01 Loading to the Workbook Options (4:08)
- 5.02 Loading to the Data Model (3:57)
- 5.03 Settings and Refresh (2:40)
- 5.04 Load to Power Pivot in 2010 (4:57)
- Exercises, Quiz, and Additional Resources
- Lesson 6: M Language
- 6.01 Introduction to M (2:24)
- 6.02 Edit and Write M (2:59)
- 6.03 Add Column with Function (7:58)
- 6.04 Reading M (5:52)
- 6.05 Formula Bar and Advanced Editor (3:35)
- 6.06 Type System (2:45)
- 6.07 Lists and Records (8:34)
- 6.08 Splitting Text (6:19)
- 6.09 Tips for Writing M (5:43)
- 6.10 Commenting Code (1:53)
- 6.11 Error Trapping (5:43)
- 6.12 If and Nested If (4:04)
- 6.13 If OR and If AND (5:09)
- 6.14 Conditional Columns (2:57)
- Exercises, Quiz, and Additional Resources
- Lesson 7: Parameter Tables and Custom Functions
- 7.01 Parameter Tables for Filtering (11:06)
- 7.02 Scrape Data from Web Pages (10:34)
- 7.03 Dynamic Folder Paths (11:40)
- 7.04 Dynamic File Paths (8:08)
- 7.05 Formula Firewall Errors (5:27)
- 7.05 Manage Parameters (13:11)
- Exercises, Quiz, and Additional Resources
- Review and Final Exam
- Final Exam
- Certificate of Completion
- Course Evaluation
|
Materials
The course includes the following course materials:
- Online lecture videos
- Sample Excel files
- Slide Handout PDF
- Glossary
- Index
- Download videos to your computer
- E-book: M is for (Data) Monkey by Ken Puls and Miguel Escobar
Objectives
- Recall how to import multiple files in a single query
- Identify the effects made to the original data source with Power Query
- State the programming language behind Power Query
Get Digital Download ” Mynda Treacy – Power Query for Excel Course ” Right Now!
Reviews
There are no reviews yet.