English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 16.5 Hours | 8.13 GB
Use Excel Power Query like an Expert from the Start! Solve Difficult Data Analysis Problems // 200+ workbooks included
Excel Power Query has been a part of Excel since Excel 2016! It’s in the Data tab under Get and Transform.
Unfortunately many people haven’t explored what it has to offer!
They continue doing things the old “manual” way, Or the “complex” way with difficult formulas.
Power Query is like an Instant Miracle Cure to your difficult data analysis problems:
- You can quickly combine and consolidate large data from different sources like Excel, text files, the web, online services, databases, even your Outlook! (Plus many more).
- You can convert downloads from your ERP system into information you can use.
- Replace complex Excel formulas with a click of a button.
- Turn complex data transformations that took forever to do (such as unpivoting data), to just a few clicks.
And these are just a few examples of what it can do!
On top, your actions are recorded so whenever you get new data, all you have to do is click Refresh!
Guess what? You can apply ALL the knowledge you learn in this course to Power BI as well!
You might be thinking…
Does it apply to me? Can I really use this at work?
If you have Excel 2016 or above, you have Power Query as a part of your Excel (Power Query is Not available for MAC).
If you work with Power BI, you also have the latest version of Power Query.
Plus learning Excel Power Query will be more fun and effortless than you think.
You’ll get the tools to make a big difference at your workplace.
The course is split in 2 parts: Essentials and Advanced.
- Just from the Power Query Essentials you’ll be able to solve many problems and make a meaningful impact in your work
- In the Excel Advanced Power Query section you’ll tackle more complex scenarios. The real world comes with messy data. You might need to write custom M functions to transform the data to the way you need. In this section you will also learn how Power Query M thinks and works.
Here are some the things you’ll accomplish:
- Import, transform and clean large data from different sources (Excel, csv, web, sharepoint etc.)
- Combine data from multiple Excel workbooks into a single Table (or Pivot Table)
- Consolidate data from all files in a folder (and make exceptions as you need)
- Understand and write your own Custom Power Query M functions to do tasks you can’t easily do with the interface
- Use Excel’s Data Model and Power Pivot to Create Relationships between your data
- Understand when to use Excel Power Query and when to Load data to Excel’s Data Model
- Create an interactive Excel Dashboard with Power Query, Data Model, Pivot Tables and slicers
- Use Power Query in Power BI and create a dashboard you share online
What you’ll learn
- Master Power Query in Excel (& Power BI)
- Create Modern Excel Dashboards with Power Query and Excel’s Data Model
- Reduce Hours of Work to One Click (Simply press Refresh)
- Automate the process of Importing, Cleaning, and Combining data
- Learn Power Query M language and create your own M Functions
- Load data into Excel from Multiple Sources such as SharePoint, csv, Web, and even Email
- Learn how to Avoid future errors by creating Robust Solutions
- Apply new concepts to real-life examples and solve complex data analysis challenges
Table of Contents
Introduction
1 Applications of Power Query
2 Important Excel Version & Expectations
3 (For older Excel versions) Workaround for Loading as Pivot Table
4 DOWNLOAD ALL Files & How to Update Source for Solution Files
POWER QUERY ESSENTIALS
5 What You Can Achieve by the End
6 What is a Proper Data Set
7 (In case you need to) Links to Brush up Your Excel
The Power of Power Query
8 Analyze Large Data Quickly! Pivot Table or Power Query
9 Power Query Overview Import Large Data from Another File
10 Power Query Editor & Basic Transformation
11 Quick Insights on Data Quality & Distribution (Hidden Feature)
12 Formula Bar, Applied Steps & M Code
13 Close & Load Destinations (How to Change Them)
14 Refresh Data & PQ Refresh Options
15 Import CSV File & Extract Text Based on Pattern
16 Merge Data with Another File (Pivot Table from Multiple Files)
17 Old School Method What if There Was NO Power Query
18 Activity – Get Familiar with Power Query
Important Power Query Tips & Tricks
19 Uploading Data From Excel
20 The Hidden Table Method (Hidden Feature)
21 Handling Changes to Source
22 Data Types in Power Query
23 Data Types vs. Formatting & Null Values
24 Power Query Navigation Shortcuts (Become Pro)
25 Finding & Correcting Errors in Data
26 More Data Views Duplicate OR Reference Query
27 Keeping an Eye on Query Dependencies
28 Delete, Manage, Copy Queries & Backup Results (Become Pro)
29 CHALLENGE – Find and Correct the Mistake
30 Test Your Knowledge – Power Query Tips & Tricks
31 Best Practice for Power Query
Helpful Power Query Transformations
32 Text Transformations (Format, Extract & more)
33 Merging Columns & What to Watch Out For
34 Fill & Replace Values to Create Proper Datasets
35 Sort Data including Multiple Levels
36 Remove Duplicates including Multiple Columns
37 Number Transformations & What to Watch Out For
38 Working with Filter (AND & OR Conditions)
39 CHALLENGE – Can you Find the Mistake Problem with Filter (Become Pro)
40 Change Type & Remove Columns Trap (Become Pro)
41 Key Takeaways – Helpful Power Query Transformations
Powerful Power Query Transformations
42 Column From Examples – Extract Patterns Quickly
43 Allocate Data to Groups or Buckets (Become Pro)
44 Conditional Columns in Power Query
45 Aggregating (Grouping) Data on Multiple Levels
46 Group By for All Rows (Become Pro)
47 Unpivot Columns – Basics
48 Unpivot & How to Overcome Common Errors (Become Pro)
49 Pivot Columns – Basics
50 Problem with Split by Delimiter (that’s easy to miss)
51 Split Column by Rows instead of Columns (Become Pro)
52 CHALLENGE Summarize Sales By State
53 Solution Summarize Sales By State (Bonus Map Chart Included)
Date & Time Transformations
54 Date Transformations (Extract Age, Weekday etc.)
55 Creating Dates from Text or Columns (Become Pro)
56 Time Transformations (Calculating Hours worked)
57 Date & Number Errors When Importing Data (Locale)
58 CHALLENGE – Pivot Table for Hours Worked by Month & Week Number
59 Solution Pivot Table for Hours Worked by Month & Week Number
60 Test Your Knowledge – Date & Time Transformations
Custom Column & Basic M Manipulation
61 Important Basic Power Query M Logic
62 Why Use Custom Columns
63 Introduction to Add Custom Column
64 Custom Columns Type Compatibility & Intrinsic Functions
65 Skipping Steps in Power Query (Become Pro)
66 Adjusting FILTER & Conditional Columns to Reference a Dynamic Variable
67 Drill-Down in Power Query
68 Custom Formulas for Template Creation (Hidden Tip)
69 Test Your Knowledge – M Basics
70 Key Takeaways – M Basics
Power Query Online Data Sources (Online)
71 Connecting to different Sources
72 Import Data from a Website
73 Automatically Connect to Files on Websites (Hidden Feature)
74 Import Data from ODATA
75 Get Google Sheet Data with Power Query
76 Connect to Outlook Online (Microsoft Exchange)
77 Connect to SharePoint or OneDrive for Business
78 Pro Tip How to Change Source from Local to SharePoint
79 CHALLENGE – Google Sheets Survey Your Dinner Plans
Combining Appending Data
80 Why Append Data The Difference Between Merge & Append
81 Combine Append Data from Multiple Workbooks
82 Combine All Files in a Folder (with Excel Tables)
83 Combine All Files in a Folder (Without Excel Tables)
84 How to Adjust Folder Path from Local to SharePoint Drive
85 Combine All Sheets in a File (Pivot Table from Multiple Sheets)
86 Overcome Potential Errors when Combining Sheets (Become Pro)
87 Consolidate Data from Multiple Sheets in the Current Workbook
88 CHALLENGE – Consolidate Data from Folder (with a twist)
89 Solution – Consolidate Data from Folder (with a twist)
90 Tips & Key Takeaways When Appending Data in Power Query
PROJECT – Report on HR Data
91 Test Your PQ Knowledge by Completing This Project
92 Calculate Years Worked in Each Position
93 Calculate Years Worked in the Company
94 Split First, Last & Middle Names to Separate Columns
95 Key Takeaways – Data Analysis & Preparation with Power Query
Merge Options – Join Kind Options Explained
96 Overview of Merge Options and Join Kinds
97 Left Outer Join & Right Outer Join
98 Merge Based on Multiple Columns (Become Pro)
99 Can You Find the Mistake Merging Text Columns
100 Merge Data to Get Multiple Match Results & Bonus Tip
101 Inner & Full Join in Power Query
102 Left & Right Anti Join when Merging in Power Query
103 How to Use Fuzzy Match in Power Query
104 Fuzzy Match with Transformation Table (Hidden Feature)
105 CHALLENGE – Someone Hacked the Salary File – Find Mismatches in Data
106 Solution – Someone Hacked the Salary File – Find Mismatches in Data
107 Key Takeaways – Power Query Merge Kinds
When to Use Power Pivot & Load to Data Model
108 When to Load Data to the Data Model
109 Availability of Power Pivot
110 Pivot Table from Multiple Excel Tables
111 Power Pivot Table with Data Model & Power Query
112 Create a Calendar Table in Power Pivot
113 Pivot Slicers & TimeLine with Power Pivot & Power Query (Become Pro)
114 Key Takeaways – Data Model & Power Query
POWER QUERY ADVANCED TO PRO
115 What You Can Achieve as an Advanced Power Query User
116 You’ve Come so Far!
Solving Complex Data Analysis Problems with Merge
117 Learning Objectives for this Section
118 Messy Data from Multiple Rows to One Row (Create Proper Data Set)
119 Search and Replace Bulk Values
120 Calculate Value Difference to Previous Row
121 Approximate Match Lookup with Merge
122 CHALLENGE – Create a Report based on Multiple Parameters
123 Solution – Create a Report based on Multiple Parameters
Advanced Problem Solving using the Power Query Interface
124 Learning Objectives for this Section
125 Assign Unique Number to Group
126 Advanced Unpivot Techniques
127 Advanced Pivot Techniques
128 Incremental Data Load & Self Referencing Query (Hidden Tip)
129 Key Takeaways – Advanced Problem Solving
PROJECT – Create a Modern Excel Dashboard with Power Query & Pivot Tables
130 Excel Dashboard Project Overview
131 Preprare a Draft of the Dashboard
132 Import Master data from External Workbook with Power Query
133 Import Data from Text File with Power Query
134 Create the Data Model & Define Relationships in Power Pivot
135 Create Logic for Latest and Previous Month in Power Query
136 Setup Calculations with Pivot Tables for Latest Month
137 Link Excel Shapes to Data & Linked Picture Trick
138 Top 3 Sales Managers & Numbers (Sorted Excel Pivot Table)
139 Linked Table for Sales by Product Category
140 Excel Pivot Chart for Monthly Sales
141 Pivot Slicer Connected to Multiple Pivot Tables
142 Finalize the Excel Dashboard
143 Key Takeaways – Excel Dashboard Project
Understanding M Formula Language (Let, Lists, Records & Improving Performance)
144 M Language – How M Thinks (let Expression & Values)
145 Defining & Invoking Custom M Functions
146 Reference Guide for Standard M Functions
147 Lists & Records and how to Reference a Specific Cell in a Table
148 Summarizing Brackets & Lookup Operators in M code
149 Creating Lists, Tables & Records inside Power Query
150 Understand Each Keyword & the Purpose of (underscore)
151 Using Power Query Parameters
152 IF Then & Lookup Operators to Lookup Values in Previous Row
153 Error Handling – Bulk Replace Lookup with Try Otherwise
154 Speed up Queries – Table.Buffer & How to Test Impact (Hidden Tip)
155 Query Folding – Improve Performance for Relational Databases
156 Test Your Knowledge – Understanding M
157 Key Takeaways & Power Query M Formula Language Specifications
Working With List & Table Functions
158 What You Learn in this Section
159 Create a Calendar Table From Start to End Date
160 Power Query Text Functions (Text.Contains, Text.Replace)
161 How to Use List Functions in Custom Column
162 Merge Values from Rows into One Cell (Become Pro)
Creating & Invoking Custom Functions
163 What You Learn in this Section
164 Unpivot & Consolidate Data From Multiple Sheets (with Custom Function)
165 Rank by Group (Nested Rank) with Power Query
166 Running totals by Month (Invoking functions)
167 How Far You’ve Come!
Flexible Importing of Data with the Help of M Functions
168 What You Learn in this Section
169 Better Collaboration – File Path as Parameter in Power Query
170 Better Collaboration – File Path from a Cell (Formula.Firewall Error)
171 Import from Folder Mixed Letter Case Headers & Trailing Spaces
172 Import All Sheets BUT Data Starts from Different Rows
173 CHALLENGE – Combine Sheets & Get Name from Cell Value
174 Solution – Combine Sheets & Get Name from Cell Value
PROJECT – Power Query and Power BI
175 Project Overview of Power BI Dashboard
176 Install Power BI Desktop & Quick Overview
177 Upload & Transform Data with Power Query in Power BI
178 Setup Data Model & Create Relationships in Power BI
179 Create Your Dashboard & Reports within Minutes
180 Publish Dashboard to Power BI Service
181 Key Takeaways – Power Query & Power BI
Final Words
182 Wrapping up & What’s Next
183 What You’ve Achieved!
184 BONUS Lecture More from Leila
Resolve the captcha to access the links!