DATALYZE .

in
Analyze…Visualize…Finalize.

POWER
QUERY
Training from Beginner to Intermediate level

[Link] CA. Abhay Gadiya

 New features in Excel
PowerPi PowerVi
vot ew

Classification: Confidential
 Big Picture

Classification: Confidential
 How to get these software

Classification: Confidential
 Activate Power Query
• Go to File > Options > Add-ins > Com-Add-ins > Power Query

Classification: Confidential
 Why Power Query?

Excel
Formul
as

SQL VBA

Classification: Confidential
 Query Stages

Power Query - ETL

Extract Transform Load
• Import Data • Columns • Excel tables
from multiple • Rows • Connection
sources • List, Records, only
• Delimited files Tables • Power Pivot
– CSV, TXT • Merge Queries Data Model
• Excel files • Append
• Folders Queries

Real magic
happens here
Classification: Confidential
 Power Query Learning Curve

Custom M
functions
Parameters

Power
Query
M Functions Objects
Merge
queries
Merge,
append,
Using consolidate
transformat data
Filtering, ion from
sorting data ribbon
Connecting Covered in
to Data
sources this course

Classification: Confidential
 Index
 Section 1 – Intro to Power Query  Section 6 – Merge Queries /
 Section 2 - Import Data into Joins
Power Query  Section 7 – Building Block for M
 Section 3 – Load Data and language
Refresh Power Query  Section 8 – Case Studies – Part
 Section 4 – Data Transformation 1
 Column Transformation  Section 9 – Power Query
 Row Transformation Objects
 Built in transformation  Lists
 Records
 Section 5 – Consolidate /
Append Data
 Tables and Others
 Section 10 – Advanced Case
Classification: Confidential Studies
 Power Query different version

Classification: Confidential
 Power Query layout
Query
Formula
editor
Bar
ribbon

Queries
Pane

Query
Results Settings
Pane

Classification: Confidential
 Import – Excel files

Existing Workbook Different Workbook

Dynamic
Tables Fixed range Sheets
range

• Functions used
= [Link] ()

Classification: Confidential
 Import from…

Text Tab CSV Excel Access

delimited delimited

Click here for information related to importing data from other

sources.

Classification: Confidential
 Load destination & Query Section

Refresh 3

How to do it
in Excel
2010?

Excel Table Connection PowerPivot Data > Refresh All

only Model

Classification: Confidential
 Columns Transformation

Name Add Remove Split Merge

columns
A B C D columns
A B C D E F columns
A B D F columns
A B1 B2 D F A B1 B2 DF
columns

Sort Unpivot Transpose Data type in columns

columns
C D B A columns
C D B columns
C
A D
A B
A A

Classification: Confidential
 Rows Transformation
Header Remove Remove Filter Duplicate
Row Row Row Row Row

Grouping Previous Errors in

Rows Row Row

Classification: Confidential
 Built in transform functions

Text Numbers Date Conditional

Classification: Confidential
 Multiple Queries – A/D/R
Append queries Duplicate queries Reference queries
Query 1 Query 1 Query 1

Query 2 Query 1

Query 3 Query 2

Query 4

Classification: Confidential
 Multiple queries – A/D/R
• Queries are Appended / Duplicate / Referenced and NOT
tables.
• Two or more queries can be Appended.
• Column headers should be IDENTICAL across queries while
appending queries.
• Final output of previous query will be referenced.

Classification: Confidential
 Import / consolidate from
Folders

• Functions used • List of ALL files in

= [Link]([Content])
= [Link]([Content]) Folder
• Change File Path
Classification: Confidential
 Differences
= [Link] () = [Link] ()

• Will read all the objects • NO recursion Effect

in the CURRENT excel file • Reads from the most
• Recursion Effect recently saved copy
• It pulls worksheets in
addition to ranges and
tables.

• Whatever method you choose make sure you make it future

proof and test it through a couple of refresh before you release
it.
Classification: Confidential
 Merge Tables / Queries
Table 1 Table 2

Classification: Confidential
 Merge Queries – Full Outer
Table 1 Table 2 Combined Table

Full Outer

Classification: Confidential
 Merge Queries – Full Inner (matching only)

Table 1 Table 2 Combined Table

Full Inner (matching

only)

Classification: Confidential
 Merge Queries – Left Outer
Table 1 Table 2 Combined Table

Left Outer

Classification: Confidential
 Merge Queries – Left Anti
Table 1 Table 2 Combined Table

Left Anti

Classification: Confidential
 Merge Queries – Right Outer
Table 1 Table 2 Combined Table

Right Outer

Classification: Confidential
 Merge Queries – Right anti
Table 1 Table 2 Combined Table

Right Anti

Classification: Confidential
 Recap - Multiple JOINs
Full Outer Left Outer Left Anti

Full Inner Right Outer Right Anti

(matching)

Classification: Confidential
 Building blocks for M
language
Case Sensitive Data types
EXCEL ≠ excel ≠ Excel 1234, 02/08/2016, Abhay, AdE12-ad-34

Base starts with “0” No Inteli sense in current version

Formula Syntax to be remembered
0 1 2 3 4 5 6 7 8

=#shared
Click here for details for all functions in power query
Classification: Confidential
 Frequently used functions

Text Date Conditional

Classification: Confidential
 Text functions
Text Functions Grouped

Information Comparisons Extraction Modification Membership Transformations

[Link] [Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link]
[Link]
[Link]
[Link]
[Link]

Classification: Confidential
 Date functions
Date Functions Grouped

Is in Is in
Add Day Start of End of From To Is in Next Others
Current Previous
Days Day Day Day From Record Week Week Week Is Leap Year
Weeks Day of Week Week Week From Text Text Month Month Month Month
Months Day of Year Month Month Quarter Quarter Quarter Quarter of year
Quarters Days in Month Quarter Quarter Year Year Year Week of Month
Years Year Year Week of Year
Year

Getting Todays date use…

=[Link]([Link]())

Classification: Confidential
 Conditional functions
?

Power Query functions for Excel functions…

• IF
• =if <logical text> then <result> else <alternate result>

• IFERROR
• =try <operation> otherwise

• AND
• =[Link]({[column]=criteria,[column]=criteria…})

• OR
• =[Link] ({[column]=criteria,[column]=criteria…})
Classification: Confidential
 Case Studies
1. Financial Statements
2. Payroll Data
3. Stacked Address
4. Multiple Filters
5. Rows to Columnar Data set
6. Lookup discount from multiple tables
7. Invoice details summary
8. Analyze Sales by Sales Rep, Region, Products, etc.
Classification: Confidential
 Power Query Objects
Lists

• Syntax
={1,2,3,4}
={“A”,“B”,“C”,“D”}
={1..365}

• Creating List
• Lists to Tables
• Table columns to Lists
• List of Lists
={{“Data”,“Lyze”}, {“Power”, “Query”}}

Classification: Confidential
 List functions
List Functions Grouped
Information Selection Transformation Membership Set Operations Ordering Averages Addition Numerics Generators
[Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link]
[Link] [Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link]
[Link]
[Link]

Classification: Confidential
 Power Query Objects
Records

• Syntax
=[Name=“Abhay”, Surname=“Gadiya”]

• Creating Record
• Multiple Records
• Record to Table
• Table Row to Record
• Each Table Row to Records

Classification: Confidential
 Record functions
Record Functions Grouped
Information Transformation Selection Serialization
[Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link]
[Link] [Link] [Link]
[Link] [Link]
[Link] [Link]
[Link]

Classification: Confidential
 Power Query Objects
Others

• Tables
• Values
• Binaries
• Errors
• Functions

Classification: Confidential
 Table functions
Table Functions Grouped
Construction Conversions Information Row Operations Column Operations Transformation Membership Ordering Other
[Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link]
[Link] [Link] [Link] [Link]
[Link] [Link] [Link]
[Link] [Link] [Link]
[Link] [Link] [Link]
[Link] [Link] [Link]
[Link] [Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link] [Link]
[Link]

Classification: Confidential
 Power Query Objects - recap
Lists Records Others

• Syntax • Syntax • Tables

={1,2,3,4} =[Name=“Abhay”, Surname=“Gadiya”]
={“A”,“B”,“C”,“D”}
={1..365} • Creating Record
• Values
• Creating List • Record to Table
• Binaries
• Lists to Tables • Multiple Records
• Errors
• Table columns to Lists • Table Row to Record
• Functions
• List of Lists • Each Table Row to Records
={{“Data”,“Lyze”}, {“Power”, “Query”}}

Classification: Confidential
 Additional Case Studies
• Extracting only relevant data.
• Creating a dynamic Calendar Table
• Individual Sales to Total Sales Percentage
• Multiple Filters in single cell

Classification: Confidential
[Link] Be in touch…!