Tutorialspoint
Subscription Includes
 04:45:43 of High Quality Videos
 43  Lectures
 Full Lifetime Access
 Certificate on Completion
$ 100   25

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

      Preview
      00:02:23

    • Introduction to Power Query

      Preview
      00: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?

      Preview
      00:05:55

    • How to import data from CSV, Text, Access files?

      00: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

Abhay Gadiya

More Courses from Abhay Gadiya