Excel Power Query Mastery -prepare data for Power BI Tableau
4.4 (46 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
362 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel Power Query Mastery -prepare data for Power BI Tableau to your Wishlist.

Add to Wishlist

Excel Power Query Mastery -prepare data for Power BI Tableau

Masterclass to get & transform data for data analytics in Tableau 10.3, Power BI, Power Pivot (DAX), Excel 2010 - 2016
Best Seller
4.4 (46 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
362 students enrolled
Created by Abhay Gadiya
Last updated 6/2017
English
Price: $55
30-Day Money-Back Guarantee
Includes:
  • 5 hours on-demand video
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • 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.
View Curriculum
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

***********************************************************************************************************************************

New course on POWER QUERY for beginners to intermediate level with extensive coverage on different features.

***********************************************************************************************************************************

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.

Power Query is a tool...

  • Tool that is very easy to learn even for novice excel users.
  • A tool that can extract / connect to data from almost any source.
  • Merge and shape data sourced to match your data analysis requirements
  • Prepare your data for further analysis and modelling by tools like Power Pivot, etc.
  • Keeps sequential record of all the steps performed from source till final output
  • You can review your automation steps later and easily modify it.

Thus, you can focus on turning data into insight and adding true value to your company and to yourself as well.

The best part is that most of the automation features in Power Query are located within its ribbon so you have to just click it with mouse and start your automation in Excel. This GUI is one of the most intuitive interfaces I have found in any tool yet. 

For learning about Power Query you do not need to learn or have prior knowledge about any programming languages like Excel VBA  or SQL, etc.

If you are planning to implement Data Analytics or Business Intelligence solutions using Power Pivot, Power View, Power Map, Power BI, Tableau, Python or R, etc. then you must learn Power Query before all these other tools.


Why should you learn Power Query?

Now let's try to relate how Power Query can be useful to you. See if you answer positively to any one of the questions enumerated below:

  • Do you work with data on daily basis?
  • Do you regularly build multiple dashboards?
  • Do you need to import data for above reporting from different sources?
  • You have to perform extensive data cleansing before your perform analysis?
  • Do you create Management Information Systems (MIS) reports regularly? 
  • Are you working as part of any Data Analytics Projects / Power BI / Tableau / Business Intelligence Projects?
  • Do you use complex / advanced Excel formulas / Excel VBA coding (macros) / SQL?

You might need to perform any of the following activities before data becomes usable:
  • Filtering out data you don't need
  • Deleting / sorting rows, columns or blanks
  • Transforming text, numeric, and date columns.
  • Removing spaces and special characters from your data.
  • Fixing the layout into tabular format so that it will work in Pivot Tables
  • Renaming, adding / removing, Splitting / joining and reordering columns
  • Creating calculating columns / fields which never existed in the source data.
  • Merging / consolidating / appending data from Text files, CSV files or database tables.
  • Incorporating the next month (or period) data to an existing report.
  • Perform same steps again when receive new file or data in next month
... and many more laborious repetitive tasks that consumes your day in and out.


If you have answered positive for any of the questions or your job looks similar then you have come to right place - Power Query Training. It does not matter if you are a novice Excel user, or Advanced Excel user, you regularly code VBA / macros to automate repetitive task in Excel, this Power Query Training course is must for you.

In this course as a beginners you will learn all the features from scratch. And after completing this course you would be proficient in using almost 80% of features in Power Query. This will guide to how convert dirty data into clean data which you can provide to your Data Analytics or Business Intelligence or Data Visualization Software.


Thought process behind POWER QUERY and this training course:

I have noted at numerous occasions that people are not aware about these new amazing tools introduced by Microsoft. Also, few people who have heard about it but does find proper resource to learn about using this tool. Hence i thought of publishing this Power Query Training here so that you can learn in structured manner about how to perform various data cleansing task in Excel using Power Query.

You DO NOT need any programming, Excel VBA coding or SQL knowledge to start using Power Query. After completing this course you would be proficient in using Power Query and can immediately apply concepts to your work. You will move from Power Query newbie to proficient user after this course.

I can confidently tell you that your investment in this course will be paid off within a months by way of saving you lot of efforts and time while working on Excel. And if you are not satisfied from this Power Query Training you can get your money back within 30 days of enrolling into the course.

So Enroll quickly and start learning & using Power Query.

Who is the target audience?
  • 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 Trasnform & Load (ETL) activities for business
Students Who Viewed This Course Also Viewed
Curriculum For This Course
42 Lectures
04:45:43
+
Instructor Introduction
1 Lecture 02:23

This video gives brief introduction about me and my work experience.

Preview 02:23
+
Power Query Introduction
1 Lecture 14:28

In this video I have briefly described what is Power Query?, Why you should learn it? What is being covered in this Power Query Training course?

Preview 14:28
+
Import Data into Power Query
2 Lectures 12:50

As a first step to start using Power Query you need to learn how to import data into Power Query. In this video of Power Query training I have explained steps to import data from existing Excel file into Power Query for - Excel table, Named Ranges and Dynamic ranges in Excel sheet.

Import Data from Excel File
05:55

In this video of Power Query Training I have explained steps for import data from different types of files viz.- CSV, Text files, Other excel files, Access files, etc.

Import data from other files / folder
06:55
+
Loading and Refresh
1 Lecture 07:44

After your perform your transformation steps within Power Query you would need to load that data back either as Excel table into sheet or store it as connection only or directly load it to PowerPivot Data model. The required steps for this are explained in this video.

Load data and refersh query when source data changes
07:44
+
Data Transformation
2 Lectures 16:20

What are the different types of column transformation that can be performed in Power Query using Ribbon interface are explained here viz. Unpivot columns using Power Query, Transpose Columns, Merge or Split Text in columns, etc.

Column Transformation
08:59

Row transformation like promoting first row as header, remove duplicate rows, grouping rows which are equivalent to sumifs in excel are explained in detail in this Power Query Training video.

Row Transformation
07:21
+
In built transformation in Power Query
4 Lectures 15:04

There are various inbuilt Text transformation in Power Query ribbon viz. Lower / Uppercase / Proper / First / Last / Middle character extraction, etc. These Text transformation have been explained in this video.

Text Transformation
07:02

How you can convert import numbers, adding / multiplying / dividing / rounding imported numbers using Power Query built in Number transformation from ribbon at explained in this video.

Numbers transformation
03:09

You can extract year, start of the year / quarter / month, end of the year / quarter / month, Quarter, month, month name, day, day name, etc. using Power Query ribbon based commands. These are explained this video.

Date transformation
02:43

How do you add a column based on fulfillment of particular condition from existing or other columns using Power Query ribbon interface is explained here. This is equivalent to IF function within Excel.

Conditional Column transformation
02:10
+
Consolidate and Append Data
6 Lectures 31:34

How to append data stored in multiple sheets within existing Excel file is been explained in this video.

Append data from Excel tables within same Excel file
02:49

You might have imported data from multiple sources and have stored each instance as separate query. How to append data from these multiple queries is being explained in this video.

Append / Duplicate / Reference multiple Queries
11:33

In case you want to import various files stored in a single folder in one query itself rather than separate individual queries is being explained in this video. You can import all or selected files from a folder in one query itself.

Import data from files in a folder
07:29

In case you want to import data only from Excel files stored in a particular folder which contains other files as well then you would need to follow the steps mentioned in this video.

Import data from multiple excel files in a folder
05:18

Instead of importing the data stored in files in a particular folder you want to create a list of ALL the files stored in that folder just to keep track of what is being received and what is pending then watch this video. This will explain how to create dynamic list of files stored in particular folder.

Preview 02:27

Now when you share the file which contains data imported from source other than existing file to your colleague or other person then the link to that source will be broken. Other person will have to change the source manually. The steps required to change the source is being explained in this video.

Changing file path for source data
01:58
+
Merge Queries / Multiple Joins in Power Query
3 Lectures 26:16

Other than appending the data you want to consolidate or join the data. This same as applying VLOOKUP in Excel. However, these joins are more robust, faster and easy to change compared to other lookup functions in excel. This video will explain theory about different joins in Power Query.

Multiple JOINS in power query
08:19

In this video with the help of examples you will understand how you can perform joins using single or multiple columns in Power Query. Type of joins covered are Left Join, Right Join, Left Anti Join, Right Anti Join, Full outer join and Inner join (matching rows). This video will explain all the steps required to perform merging Power Queries.

Different Joins in Power Query - Part 2
11:47

In addition to six types of joins there are situation where you would need to perform Cross Join or Cartesian Product of tables. These types of joins are useful for analyzing Events in Progress types of analysis. We will lookout at workaround in Power Query to perform this type of Cross Joins.

Cross Joins / Cartesian Product of Two tables
06:10
+
Building blocks for M language
4 Lectures 27:07

This video will explain about some theory about M Language or Power Query Formula syntax.

Introduction to building blocks in M
05:11

Other ribbon based Text transformation commands there are large number of Text Power Query Formulas. Frequently used Power Query Text formulas are explained in this video.

Text functions in Power Query
08:00

Power Query also has various formulas that can work on Dates. Some of the frequently used formulas are explained in this video.

Date functions in Power Query
06:53

What are the Power Query Formulas which are Excel Equivalent functions - IFs, AND, OR & IFERROR are shown in this video with the help of an example.

Conditional Functions in Power Query
07:03
+
Case Studies - Part 1
8 Lectures 01:07:13

In this first case study you want to convert the Financial statement data for multiple years into Tabular format. How do you apply all the power query knowledge gained till now is explained in this and other case studies.

Financial Statements
07:14

How do you convert the Payroll data extracted from payroll system into proper tabular format is explained in this video.

Payroll Data
06:40

You have a address book which contains data for each person stored in single row and stacked above each other. This video will explain the steps required to convert the stacked data into proper tabular format.

Address Book
06:42

You have a data set and then set of values which you want put as filter on the source data. These are multiple values stored as separate table. How to apply multiple value filters on single column is explained in this video using Merge query option.

Multiple filters on single column
06:23

You have a data set which contains details stored in set of two rows. How you can convert those into Tabular Format using Power Query is explained here.

Rows to columnar data set
05:17

You have multiple lookup tables through which you need to calculate sales. How to perform this task using Power Query and getting data from multiple lookup values is explained in this video.

Looking up discount rates
09:40

You have sales data extracted from the system and someone else had created a pivot table on it. Now you do not have that source file and you need to convert this pivoted data using Unpivot function in Power Query is explained in this video.

Invoice data
04:43

Other than just performing Data cleansing activities, Power Query can also perform various Data Analytics and first level statistical analysis as well. How you can perform multiple SUMIFS, MAXIFS, MINIFS, MEDIANIFS in power query is explained in this video.

Sales analysis (MaxIF, MinIF, MedianIF, SumIF) from multiple tables
20:34
3 More Sections
About the Instructor
Abhay Gadiya
4.4 Average rating
46 Reviews
362 Students
1 Course
Professional Auditor - expertise in Audit & Data Analytics


I am qualified Chartered Accountant from India and have completed Diploma in Information System Audit (Indian equivalent exam for CISA).

I have circa 14+ years of experience into Data Analytics, Internal Audits, External (Statutory) Audits, SOX Compliance, Risk Assessment and Regulatory Compliance.  I have audited various entities under BFSI, Automotive Manufacturing, ITES, Infrastructure and Pharmaceuticals sectors. I have worked circa 9 years with Big4 consultancy firms – Deloitte, Price Waterhouse and other with Bank of New York Mellon.

I have conducted various workshops at Institute of Chartered Accountants of India. Approximately 600+ people have attended these workshops and have given extremely positive feedback.