POWER QUERY

MEET POWER QUERY

Power Query (aka “Get & Transform”) allows you to:

• Connect to data across a wide range of sources
• Filter, shape, append and transform raw data for further analysis and modeling
• Create stored procedures to automate your data prep (like a macro!)

The Power Query tools live in the Data tab, under

the “Get & Transform” section (Excel 2016)
 TYPES OF DATA CONNECTIONS

From File From Database FromAzure From Online Services From Other Sources
 THE QUERY EDITOR

Query
Editing
Tools
Formula Bar
(this is “M” code)

Name your
table!
Data
Preview Applied
Steps

Access the Query Editor by creating a new query and choosing the “Edit” option, or by launching
the Workbook Queries pane (Data > Show Queries) and right-clicking an existing query to edit
 QUERY EDITOR TOOLS
The HOME tab includes general settings and common table transformation tools

The TRANSFORM tab includes tools to modify existing columns (splitting/grouping, transposing, extracting text, etc.

The ADD COLUMN tools create new columns based on conditional rules, text operations, calculations, dates, etc.
DATA LOADING OPTIONS

When you load data from Power Query, you have several options:
• Table
• Stores the data in a new or existing worksheet
• Requires relatively small data sets (<1mm rows)

• Connection Only
• Saves the data connection settings and applied steps
• Data does not load to a worksheet

• Add to Data Model

• Compresses and loads data to Excel’s Data Model
• Makes data accessible to Power Pivot for further analysis
 BASIC TABLE TRANSFORMATIONS

Sort values Change data types Promote header row

(A-Z, Low-High, etc.) (date, $, %, text, etc.)

Duplicate, move &

rename columns
Keep or remove columns
Tip: Right-click the
Tip: use the “Remove Other column header to
Columns” option if you always access common tools
want a specific set
Keep or remove rows
Tip: use the “Remove Duplicates”
option to create a new lookup
table from scratch
 TEXT-SPECIFIC TOOLS

Extract characters from a text

column using a fixed length,
first or last, or a defined range
Split a text column based on Tip: Select two or more columns to
either a specific delimiter or merge or concatenate fields
a number of characters

HEY THIS IS IMPORTANT!

You can access many of these tools in both the
“Transform” and “Add Column” menus -- the Format a text column to upper, lower or
difference is whether you want to add a new proper case, or add a prefix or suffix
column or modify an existing one Tip: Use “Trim” to eliminate leading & trailing spaces,
or “Clean” to remove non-printable characters
 NUMBER-SPECIFIC TOOLS

Information tools allow

you to define binary flags
(TRUE/FALSE or 1/0) to
Standard Scientific Trigonometry mark each row in a
Statistics functions allow you to column as even, odd,
evaluate basic stats for the selected Standard, Scientific and Trigonometry tools allow you
positive or negative
column (sum, min/max, average, to apply standard operations (addition, multiplication,
count, countdistinct, etc) division, etc.) or more advanced calculations (power,
logarithm, sine, tangent, etc) to each value in a column
Note: These tools return a SINGLE value,
and are commonly used to explore a table Note: Unlike the Statistics options, these tools are applied to
rather than prepare it for loading each individual row in the table
 DATE-SPECIFIC TOOLS

Date & Time tools are relatively straight-forward, and include the following options:
• Age: Difference between the current time and the date in each row
• Date Only: Removes the time component of a date/time field
• Year/Month/Quarter/Week/Day: Extracts individual components from a date field
(Time-specific options include Hour, Minute, Second, etc.)
• Earliest/Latest: Evaluates the earliest or latest date from a column as a single value (can
only be accessed from the “Transform” menu)

Note: You will almost always want to perform these operations from the “Add Column” menu to
build out new fields, rather than transforming an individual date/time column

PRO TIP:
Load up a table containing a single date column and use Date tools to build out an entire calendar table
 CREATING A BASIC CALENDAR TABLE

Use pre-defined Date options

in the “Add Column” menu to
quickly build out a calendar
table from a list of dates
 PRO TIP: CREATING A ROLLING CALENDAR

1) Create a new, blank query (Data > New Query > From Other Sources > Blank Query)

2) In the formula bar, generate a starting date by entering a “literal” (1/1/2013 shown below):

3) Click the fX icon to add a new custom step, and enter the following formula exactly as shown:

4) Convert the resulting list into a Table (List Tools > To Table) and format the column as a Date

5) Add calculated Date columns (Year, Month, Week, etc.) as necessary using the Add Column tools
 ADDING AN INDEX COLUMN

Index Columns contain a list of

sequential values that can be used to
identify each unique row in a table
(typically starting from 0 or 1)

These columns are often used to

create unique IDs that can be used to
form relationships between tables
(more on that later!)
 ADDING A CONDITIONAL
COLUMN

Conditional Columns allow you to define new fields based

on logical rules and conditions (IF/THEN statements)

In this case we’re creating a new conditional column

called “Order Size”, which depends on the values in the
“quantity” column, as follows:
• If quantity >5, Order Size = “Large”
• If quantity is from 2-5, Order Size = “Medium”
• If quantity =1, Order Size = “Small”
• Otherwise Order Size = “Other”
GROUPING & AGGREGATING DATA

Group By allows you to aggregate your data at a different level

(i.e. transform daily data into monthly, roll up transaction-level data by store, etc.)

In this case we’re transforming a daily, transaction-level table into

a summary of “quantity” by “product_id”

Note that we lose any field not specified in the Group By settings
 PIVOTING & UNPIVOTING

“Pivoting” is a fancy way to describe the process of turning distinct row

values into columns (“pivoting”) or turning columns into rows (“unpivoting”)

Imagine that the table is on a hinge; pivoting is like rotating

it from a vertical to a horizontal layout, and unpivoting is
like rotating it from horizontal to vertical

NOTE: Transpose works very similarly, but doesn’t recognize

unique values; instead, the entire table is transformed so
that each row becomes a column and vice versa
MODIFYING WORKBOOK QUERIES

Click on Show Queries to launch the Workbook

Queries pane

Right-click any individual query to access

common options and tools:
• Edit (launches the Query Editor)
• Delete
• Rename
• Refresh
• Duplicate
• Merge
• Append
MERGING QUERIES

• Merging queries allows you to join tables

based on a common column (like VLOOKUP)

• In this case we’re merging the

FoodMart_Transactions_1997 table with
the Product_Lookup table, which share a
“product_id” column

TIP: Merging adds columns to an existing table

HEY THIS IS IMPORTANT!

Just because you can merge tables,
doesn’t mean you should.
In general, it’s better to keep tables
separate and define relationships
between them (more on that later!)
 APPENDING QUERIES

• Appending queries allows you to combine (or

stack) tables that share a common structure and
set of columns

• In this case we’re appending the

FoodMart_Transactions_1998 table to the
FoodMart_Transactions_1997 table, since they
contain the same set of columns and data types

TIP: Appending adds rows to an existing table

PRO TIP:
Use the “From Folder” query option to automatically append all files from within the same folder
 POWER QUERY BEST PRACTICES

Give your queries clear and intuitive names, before loading the data
• Define names immediately; updating query & table names later can be a headache,
especially if you’ve already referenced them in calculated measures
• Don’t use spaces in table names (otherwise you have surround them with single quotes)

Do as much shaping as possible at the source of the data

• Shaping data at the source (i.e. SQL, Access) minimizes the need for complex procedures in
Power Query, and allows you to create new models without replicating the same process

When working with large tables, only load the data you need
• Don’t include hourly data when you only need daily, or product-level transactions when
you only care about store-level performance; extra data will only slow you down