Power Query for Excel by Mynda Treacy (12 CPE)

• 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

• Author

  Mynda Treacy, Microsoft Excel MVP

• Subject

  Power Query / Get & Transform Queries

• Benefits

  Begin Now: begin training immediately
  Certificate: download and print your certificate instantly upon completion
  Pause and Resume: pause and resume training as needed
  Exam: unlimited exam attempts

• Software

  Featuring Microsoft Excel for Windows (2010 or higher required)

• Format

  On-Demand

• Access Begins

  Immediately

• Duration

  Online enrollment provides access for one year.

• Certificate of Completion

  Download instantly once a passing score on the final exam is achieved. You may re-attempt the final exam as many times as necessary.

• Enrollment Period

  Your enrollment is valid for one year.

---

 

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

Comments from participants

• “Last week I spent 6 hours to clean up and put 2 large data sets together. Today, with Power Query, it took me about 30 minutes to come to the same result by using the Append function. I obviously showed it to my boss and he seemed stunned. Thank you Mynda for creating this wonderful course.” - Enrique


• “I have really made some giant steps after joining your courses. BUT as important is your help/support regarding my problems/issues. Then I really learn a lot because I manage to relate your guidance/instructions to my job.” - Ole


• “ Power Query rocks. Yesterday I restyled a very complex data set in PQ. I created a test model with .csv files for Jan-Apr 2016 report data. Then I added a few columns needed for my PivotTable. Today I hit the Refresh button to grab May's report that was received this morning. Instantly my pivot summary includes May calculations. It's a Cheshire Cat kind-of-smile here today.” - Libby

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

Program Level

Basic

Prerequisites

None

Advance Preparation Needed

Ensure you have access to Excel for Windows, preferably versions 2010 or later. Excel for Mac is not supported.

Delivery Method

QAS Self-study

Recommended CPE Credits

12

Field of Study

Computer Software & Applications

Expiration

Program enrollment is valid for one year and participant must complete the final exam within this time period.

Registration

To register, add this course to your cart by clicking the Buy Now button and then complete the checkout process.

Refund Policy

Full 30-day refund; refund for any course cancellations or registration into upcoming session if preferred.

Complaint Resolution

For information regarding adminstrative policies such as complaint resolution, please contact Jeff Lenning CPA CITP at 949-200-7688 or via email info@excel-university.com

Official Registry Statement

Excel University, Inc. is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.