corporates.training@gmail.com
Follow us on

Online Courses in Power Query in Excel

Power Query In Excel

Duration: 3 Days

How Do We Conduct Online Corporate Training ?

 

What is POWER QUERY in Excel?

Power Query ( Inbuilt in Office 365 & 2016, Add-in for 2013 ) in Excel, is used for ETL(Extract, Transform, Load). That means, data can be extracted from different sources, and after transforming, it can be loaded into the worksheet. It can be said that POWER QUERY is a data cleansing mechanism as it has all the possible options to transform the data. It performs real-time tasks and records all the steps which are performed.

Why Should You Use Power Query ?

Different Data Sources: We can load data into power query editor from different data sources, like, CSV, TXT, MS SQL Server, JSON, Web etc.
Transform Data Easily: Normally we use formulas and pivot tables for data transformations but with POWER QUERY we can do a lot thing just with few clicks.
It’s Real-Time: Write a query for once and it can be refreshed every time when there is a change

In this course, you will learn how to harness the capabilities of Power Query to resolve your data challenges and, in the process, save up to 80% of your data preparation time.

 

Let’s take a practical EXAMPLE for Power Query:

An HR Consultant, has been provided with the below Data, where 705 employees have been asked 25 Questions regarding their opinion, about the COMPANY, and we have to analyse each of the 25 Questions, depending on their Score, Go Ahead Try it ( Nothing like a Practical Example ).

Below is the “Desired Result”
Can you Create a Pivot Table of the above DATA? If yes, TRY to get the below format

Download this file & Try to get desired results from the given DATA.

This Advanced Excel Course will empower the participants to be able to do the following:

  1. Performing complex calculations more efficiently, using various Excel functions.
  2. Organizing and analyzing large volumes of data.
  3. Creating MIS reports.
  4. Designing and using templates.
  5. Consolidating and managing data from multiple workbooks.
Power Query Course Module 1: Introduction to Power Query
  • Power Query Introduction
Power Query Course Module 2 : Import Data into Power Query
  • Import Data from Excel File
  • Import data from other files / folder
Power Query Course Module 3  : Loading and Refresh
  • Load data and refresh query when source data changes
Power Query Course Module 4 : Data Transformation
  • Column Transformation
  • Row Transformation
Power Query Course Module 5 : In built transformation in Power Query
  • Text Transformation
  • Numbers transformation
  • Date transformation
  • Conditional Column transformation
Power Query Course Module 6 : Consolidate and Append Data
  • Append data from Excel tables within same Excel file
  • Append / Duplicate / Reference multiple Queries
  • Import data from files in a folder
  • Import data from multiple excel files in a folder
  • List of files in folder
  • Changing file path for source data
Power Query Course Module 7 : Merge Queries / Multiple Joins in Power Query
  • Multiple JOINS in power query (Merge Queries)
  • Different Joins in Power Query
  • Cross Joins / Cartesian Product of Two tables
Power Query Course Module 8 : Building blocks for M language
  • Introduction to building blocks in M
  • Text functions in Power Query
  • Date functions in Power Query
  • Conditional Functions in Power Query
Power Query Course Module 9  : Case Studies
  • Self-Service BI
  • Power Pivot
  • Power Query
Power Query Course Module 10 : Power Query Objects
  • List as object and List functions in Power Query
  • Record as object and Record functions in Power Query
  • Table & other objects and Table functions in Power Query
  • Power Query Objects


    More Review
    Open chat