Power Query
Power Query - Power Query is an ETL Software in Power BI.
ETL – Extract, Transform & Load
Power Query can Extract the Data from any Kind of Data Source , Transform the Data as per the
Reporting Needs and Loads the Data into Power Pivot.
Home Tab - Transform Data - Power Query Editor UI
-----------------------
Power Query User Interface / Power Query Editor User Interface
Queries Pane - Queries Pane will Contains list of Tables we Brought into Power Query from Different
Data Sources.
View Tab - Query Dependancies
--
Data Pane / Results Pane - Data Pane will Show Selected Table Data
When we Do Transformation on Data we can See the Results in Same Data Pane . Hence we call Data
Pane as Results Pane.
----
Ribbon - Ribbon will Contains Multiple Tabs and Each Tab will Contains 100' s GUI Options which we
used for Performing ETL Activities .
---
Query Settings Pane
Properties - We will Discuss later
Applied Steps - Applied Steps will Contains all the ETL Steps we performed on a Selected Table
-----
Formula Bar - Formula Bar will Show Selected Step M Language Code .
By Default we will not See Fomula Bar
View Tab - Check Formual Bar Option
---
Advanced Editor - Advanced Editor will Show all the ETL Steps M Languuage Code .
----------------------------
How Data will be Processed in Power Query ?
How ETL Activities will Happens in Power Query?
When we Bring the Tables to Power Query it will Bring Tables and First 1000 Rows Only.
When we Do Transformation it will Perform on 1000 Rows .
When we Click on Close & Apply it will Perform Transformation on Complete Data
----------------------------------------------------
What is Datatype ? How to Change Datatype of the Columns ?
Datatype - Datatype Represents type of Information Stored in the Columns
Text Datatype - Text , Number , Special Characters & Combination of Text , Number , Special
Characters
Number Datatype - Only Numeric Information
Date Datatype - Only Date Information
-----------------------------
Filters in Power Query
Filters - When ever we need to Subset of Data from Complete Data we will use Filters
Filters in Power Query - When Data Source Contains Complete Data and if we need to load SubSet of
Data into Power Pivot we will use Filters in Power Query.
In Power Query we can Filteer the Data in two ways
1. Basic Filteing - When we have Less Distinct Values in Columns
2. Advanced Filtering
Based on Datatype of the Columns Advanced Filtering Options will Change
Filtering Text Datatype Column Data
Text Filters in Power Query is Case Sensitive
Sales Between 100 & 300
Equalent to
Sales >=100 AND Sales <=300
-------------------------
Power Query Filters - Class 1
Extra Video -
1. Date Column Advanced Filtering Option
AND
OR
How to Write Filter Conditions on Multiple Columns at a Time
---------------------------------
Unwanted Rows can be Removed using Filters - Save Space in Power Pivot
Inbuilt Column Transformations
Remove Columns / Remove Other Columns
Choose Columns, Go To Column
Name / Rename a Column
Reorder Columns or Sort Columns - NoOfDays
Column from Examples
Order Date(Year), Customer ID (Text Before -), CustomerName(First 5 Chars)
(GUI Options , Column from Examples, Custom Column)
Add Column / Custom Column - We need to Write M Language Code too Derive the Column
M Language is Case Sensitive
Duplicate Column
When you want Output in Same Column use any of the Options in Transform Tab
When you want Output in the New Column use the Options in Add Column Tab
Split Columns - Mostly we need to Split the Columns Based on Delimiters or Based on Number
of Characters
Merge Columns
Pivot Column - Rows to Columns - Table Format Data to Pivot Format - Number of Rows will
Reduce
Unpivot Columns - Columns to Rows - If You Want to Convert Pivot Format Data to Table Format
- Number of Rows will Increase
Transpose - Rows to Columns & Columns to Rows
Replace Values
Any arthematic Operation with Null is NUll
Remove Empty
--------------------------------------
In built Row Transformations
Header Row or Use First Row as Headers
Use Headers as First Row
Keep Top Rows - Whole Table
Keep Bottom Rows - Whole Table
Keep Range of Rows - Whole Table
Keep Errors - whole table or selected columns
Remove Errors - whole table or selected columns
Remove Blank Rows - Whole Table
Remove Empty - Down Arrow - Selected Column
Keep Duplicates - whole table or selected columns
Remove Duplicates - whole table or selected columns
Remove Top Rows - Whole Table
Remove Bottom Rows - Whole Table
Remove Alternative Rows - Whole Table
Group Rows / Group By
1. Few options will work on Complete Table
2. Few Options work on Complete Table as well as a specific Column
Orders Table - 10 M
1. Total Sales
2. Region Sales
--------------------------
Combine Queries - Using Combine Queries we will Combine the Tables
Database - Tables
Power Query - Queries
We can Combine the Tables in 2 Ways
1. Merge Queries - Joins
2. Append Queries - Union ALL
-----------------------
Merge Queries - Joins
When ever we need to see the Data from More than One Tables in a Single Select Statement we use
Joins.
Types of Joins
1. Cross Join / Cartesian Join
2. Inner Join / Equi Join / Natural Join
3. Left Outer Join
4. Right Outer Join
5. Full Outer Join
6. Self Join
Based on What Output we need we need to Select the Join Types / Join kind .
---------------------------------------
Cross Join / Cartesian Join
When ever we need all Combinations of Data we will Perform Cross Join - 12 Rows
EMP DEPT
EMPNO ENAME SAL DEPTNO DEPTNO
DNAME LOC
1001 SUNIL 1000 10 10 SALES
HYD
1002 SRI 2000 20 20 HR
BANG
1003 SIVA 3000 10 30 FIN
PUNE
1004 RAM 4000 50
SELECT EMP.*, DEPT.* FROM EMP, DEPT; 2 Rows
SELECT EMPNO, ENAME, SAL, EMP.DEPTNO ,DNAME, LOC FROM EMP, DEPT;
EMPNO ENAME SAL DEPTNO DNAME LOC
1001 SUNIL 1000 10 SALES HYD
1001 SUNIL 1000 10 HR BANG
1001 SUNIL 1000 10 FIN PUNE
-----------
Inner Join / Equi Join / Natural Join - Only Matching Records
EMP DEPT
EMPNO ENAME SAL DEPTNO DEPTNO
DNAME LOC
1001 SUNIL 1000 10 10 SALES
HYD
1002 SRI 2000 20 20 HR
BANG
1003 SIVA 3000 10 30 FIN
PUNE
1004 RAM 4000 50
SELECT EMPNO, ENAME, SAL, EMP.DEPTNO ,DNAME, LOC FROM EMP INNER JOIN DEPT ON
EMP.DEPTNO=DEPT.DEPTNO;
EMPNO ENAME SAL DEPTNO DNAME LOC
1001 SUNIL 1000 10 SALES HYD
1002 SRI 2000 20 HR BANG
1003 SIVA 3000 10 SALES HYD
---
***Left Outer Join - Matching Records(Inner Join Output) + Non Matching Records from Left Side Table
EMP DEPT
EMPNO ENAME SAL DEPTNO DEPTNO
DNAME LOC
1001 SUNIL 1000 10 10 SALES
HYD
1002 SRI 2000 20 20 HR
BANG
1003 SIVA 3000 10 30 FIN
PUNE
1004 RAM 4000 50
SELECT EMPNO, ENAME, SAL, EMP.DEPTNO ,DNAME, LOC FROM EMP LEFT OUTER JOIN DEPT ON
EMP.DEPTNO=DEPT.DEPTNO;
EMPNO ENAME SAL DEPTNO DNAME LOC
1001 SUNIL 1000 10 SALES HYD
1002 SRI 2000 20 HR BANG
1003 SIVA 3000 10 SALES HYD
1004 RAM 4000 50 NULL NULL
-------------------
Right Outer Join - Matching Records(Inner Join Output) +Non Matching Records from Right Side Table
EMP DEPT
EMPNO ENAME SAL DEPTNO DEPTNO
DNAME LOC
1001 SUNIL 1000 10 10 SALES
HYD
1002 SRI 2000 20 20 HR
BANG
1003 SIVA 3000 10 30 FIN
PUNE
1004 RAM 4000 50
SELECT EMPNO, ENAME, SAL, DEPT.DEPTNO ,DNAME, LOC FROM EMP RIGHT OUTER JOIN DEPT ON
EMP.DEPTNO=DEPT.DEPTNO;
EMPNO ENAME SAL DEPTNO DNAME LOC
1001 SUNIL 1000 10 SALES HYD
1002 SRI 2000 20 HR BANG
1003 SIVA 3000 10 SALES HYD
NULL NULL NULL 30 FIN PUNE
------------
Full Outer Join - Matching Records(Inner Join Output) + Non Matching Records from Left Side Table +
Non Matching Records from Right Side Table
EMP DEPT
EMPNO ENAME SAL DEPTNO DEPTNO
DNAME LOC
1001 SUNIL 1000 10 10 SALES
HYD
1002 SRI 2000 20 20 HR
BANG
1003 SIVA 3000 10 30 FIN
PUNE
1004 RAM 4000 50
SELECT EMPNO, ENAME, SAL, DEPT.DEPTNO ,DNAME, LOC FROM EMP FULL OUTER JOIN DEPT ON
EMP.DEPTNO=DEPT.DEPTNO;
EMPNO ENAME SAL DEPTNO DNAME LOC
1001 SUNIL 1000 10 SALES HYD
1002 SRI 2000 20 HR BANG
1003 SIVA 3000 10 SALES HYD
1004 RAM 4000 NULL NULL NULL
NULL NULL NULL 30 FIN PUNE
-----------------------------------
T1 T2
C1 C1
--- --
1 1
2 1
2 2
3 4
1. Cross Join / Cartesian Join - 16 rows
2. Inner Join / Equi Join / Natural Join - 4 rows
3. Left Outer Join - 5 rows
4. Right Outer Join - 5 rows
5. Full Outer Join - 6 rows
Inner Join / Equi Join / Natural Join
The Default Join Type in Power Query is Left Outer Join
Left Anti Join - Only Non Matching Records from Left Side Table
Right Anti Join - Only Non Matching Records from Right Side Table
-----------------------
1. Cross Join / Cartesian Join
2. How to Join the Tables using More than One Join Condition
3. How to Join More than 2 Tables
Table 1 , Table 2 & Table 3
Table 1 Join Table 2 - Result Join Table 3 - Result
4. Use Fuzzy Matching
When we Join the tables on Text Coumns then Use Fuzzy Matching will Come into Picture .
5. What is Self Join and How to Perform Self Join
-------------------------
Merge Queries - Videos
Append Queries - Union ALL - Video
------------------------------
Query Options
Enable Load
When we Use Combine Queries we no need to Load Source Tables. For Source Table Uncheck Enable
Load
-----------
Include in Report Refresh
In the Source there will be Some Tables where Data will not Change we call them as Static Tables
For Static Tables Uncheck include in Report Refresh Option.
----------------------------------
Difference Between Duplicate & Reference
Duplicate Tables is an Independent Table of Source Table
Reference Tables is Dependent Table on Source Tbale
-----------------------
Home Tab Options
Close Apply - Will Commit ETL Changes to Power Pivot and Close Power Query Software .
Apply - Will Commit ETL Changes to Power Pivot
Close -Close will Close Power Query Software with Out Commiting Changes.
-------
Recent Sources - Recent Sources will Store Recently Connected Data Sources Connection Information
---
Enter Data - Using Enter Data we can Create Tables
--
Data Source Settings - Data Source Settings will help us to Change Data Source Connection Information
Video - Parameters
1. Manage Parameter
2. Edit Parameter
3. New Parameters
Refresh Preview - Refresh Preview will Refresh Only Selected Table
-----------
Sorting will help us to present the Data in Ascending or Descending Order.
Data Analyst - SQL + Power BI
Data Scientist - SQL + Power BI
------
Transform tab Options
Number Column, Text Column & Date Column Transfirmations
--------------
Invoke Custom Function
Every GUI Option is a Default Function
Custom Function - When ever I need to Reuse Some ETL Login in Many Places , if there No Predefined
Function then Creata a Custom Function with the ETL Logic and Use it when ever you need .
= (Num1 as number, Num2 as number, Num3 as number)=>(Num1+Num2)*Num3
