Excel Power Query Training - Beginners to Advanced level
Created by Abhay Gadiya, Last Updated 13-Jan-2020, Language:English
Excel Power Query Training - Beginners to Advanced level
Data modelling using get & transform for business intelligence and data analytics in Excel 2010 -2016 using Power Query
Created by Abhay Gadiya, Last Updated 13-Jan-2020, Language:English
What Will I Get ?
- At the end of this Power Query Training you would understand 80% of features available in Power Query
- You would be able to perform various complex data cleansing activities using Power Query
- You would be able to automate various task which would have taken long hours of work.
Requirements
- You should have basic knowledge of using Excel.
- You should be aware about Table feature in Excel.
- You should have Excel 2010 / 2013 / 2016 version installed in your PC.
- You would need to install Power Query if you are using Excel 2010 or 2013. (will be shown during the course intro)
Description
What is POWER QUERY (PQ)?
It's a FREE tool provided by Microsoft for Excel users.
Yes you read it correctly. This tool is FREE !!!
And Power Query can make novice Excel user to PRO-Excel user very quickly & this training will make your journey easy and memorable.
Power Query is available for ALL users of Excel 2010 / 2013 / 2016 and PowerBI. In Excel 2016 it has been renamed as "Get & Transform".
This course topics can be applied for all versions of Power Query - Excel 2010 / 2013 / 2016 & Power BI.
Power Query is a tool that can be used for data discovery, reshaping the data and combining data ( Merge / Blend / Consolidate, etc.) coming from different sources. Power Query can connect directly to Excel, Access, Text, CSV, Hadoop, Sales Force, Azure and many other different sources of data.
You can perform and automate all the Extract > Transform > Load (ETL) tasks required to clean the data and make it usable for further analysis in Tableau, Power BI, Excel, Access, etc.
Who this course is for:
- Novice Excel user to Advanced Excel users
- People performing Data Analytics in Excel, Power Pivot, Power BI, Tableau, Python, R
- Business Intelligence Specialists using Excel, Tableau, Power Pivot, Power BI, Python, R
- Data Analysts / Financial Analysts using Excel, Tableau, Power Pivot, Power BI, Python, R
- If you or your team needs to fetch data from different sources and transform it so that it can be used in Excel for further analysis, then this course will help you master Power Query features from scratch
- IT specialists who performs Extract Transform & Load (ETL) activities for business
Course Content
-
Instructor Introduction
3 Lectures 00:16:51-
Instructor Introduction
Preview00:02:23 -
Introduction to Power Query
Preview00:14:28 -
Supporting Excel Files
-
-
Getting Started with Power Query
3 Lectures 00:20:34-
How to import data into Power Query from Excel file?
Preview00:05:55 -
How to import data from CSV, Text, Access files?
Preview00:06:55 -
Loading data from Power Query back to Excel sheet
00:07:44
-
-
Data transformation without any formulas inside Power Query
6 Lectures 00:31:24-
How Rename, add, split, merge, sort, unpivot columns inside Power Query
00:08:59 -
How to keep or remove - empty / duplicate rows, define header row, filter rows?
00:07:21 -
How to perform left, right, mid, len formulas on Text data inside Power Query
00:07:02 -
How to perform addition, subtraction, multiplication, division inside Power Query?
00:03:09 -
How to get date related information like Day name, month name, Year, Quarter, etc.?
00:02:43 -
Writing conditional IF type formulas inside Power Query using user interface.
00:02:10
-
-
Working on data from multiple sources
6 Lectures 00:31:34-
No more manual copy and paste to append data from multiple sheets, files, etc.
00:11:33 -
How to append tables from existing active Excel file itself
00:02:49 -
Working with ALL files stored in a folder
00:07:29 -
Import data from Excel files in a folder
00:05:18 -
Creating dynamic inventory of all the files stored in a folder.
00:02:27 -
Workaround for issue while sharing files with Power Query
00:01:58
-
-
I have stopped using VLOOKUP function - want to know why?
3 Lectures 00:26:16-
Limitation of VLOOKUP and how to overcome them
00:08:19 -
Use Six different type of Joins as alternative to VLOOKUP
00:11:47 -
Perform complex CROSS join of multiple dataset with NO common field
00:06:10
-
-
On the way to become superstar in Power Query - Learn basics of M Language
6 Lectures 00:36:18-
Fundamentals of M Language and its characteristics
00:05:11 -
Learn using lot more M function for Text
00:08:00 -
How to use date function from M library
00:06:53 -
Performing complex conditional logic with AND / OR logic checking
00:07:03 -
Conditional Formulas key word - And Or
00:04:13 -
Columns from Example feature - Artificial intelligence built in Power Query - Auto detect required formulas
00:04:58
-
-
Apply the knowledge gained to real world datasets - see the immediate difference
8 Lectures 01:07:13-
How to convert multiple year financial data converted to tabular format
00:07:14 -
How to transform monthly payroll data for 1000s of employee with simple click of button
00:06:40 -
You data stacked up one above another - no worries Power Query is there for you
00:06:42 -
No more manually applying multiple items in filter drop downs
00:06:23 -
How data stored in rows can be converted into columns - more complex than transpose
00:05:17 -
Picking up data stored in multiple tables and getting discount amount
00:09:40 -
Dirty invoice data converted to perform sales analysis
00:04:43 -
Performing Sales analysis using data from multiple tables
00:20:34
-
-
Increase your level with regards to M Language
4 Lectures 00:25:23-
Elements of Power Query M Language - Lists
00:11:42 -
Elements of Power Query M Language - Records
00:09:16 -
Elements of Power Query M Language - Tables
00:03:10 -
Recap of fundamental objects of Power Query
00:01:15
-
-
Apply your M Language knowledge to complex data from real world
4 Lectures 00:30:10-
Text files - ignore top / bottom rows and extract relevant data in the middle
00:12:43 -
Prepare Calendar table by providing Start and End date with all other information
00:05:57 -
Calculating ratio of Individual Sales to total sales inside Power Query
00:07:56 -
Second approach to applying multiple filters on a single column
00:03:34
-