Power Query
Import data in Excel, Power Pivot, Power BI
Marco Russo, SQLBI
[email protected]Alberto Ferrari, SQLBI
[email protected]#SQLSAT454
Sponsors
#SQLSAT454
Agenda
What is Power Query
Connectors
Transformations
The M Language
Query Folding
#SQLSAT454
WHAT IS POWER QUERY
#SQLSAT454
What is Power Query
Excel Add-In for Excel 2010 and Excel 2013
Free download
Features
Data Discovery
Data Transformation
Hidden features
Language M
#SQLSAT454
Power Query Elements
Connectors
Transformations (UI)
The M Language
Sharing Queries and Functions
#SQLSAT454
CONNECTORS
#SQLSAT454
Connectors
#SQLSAT454
TRANSFORMATIONS
#SQLSAT454
Transformations
#SQLSAT454
Combine Table Operations
Merge
Like a LEFT JOIN
Only include matching Rows
= INNER JOIN
Append
Like a UNION
Matches columns with the same name
#SQLSAT454
Reduce Filter Rows
Filters useful to import from reports
Remove Duplicates at column level
First value found on other columns
#SQLSAT454
Sort Order by
Applied to one column at a time
Every step in a query keeps data order
If undefined, sort order depends on data
source
#SQLSAT454
Transform
Data Type: type cast
Split Column: fixed length or separator
Unpivot: normalize N column in N rows
#SQLSAT454
Create Insert Columns
Merge Columns concatenate values with a
separator
Insert Index Column row number
Sum/Product sum or multiply by row
#SQLSAT454
Advanced Editor
A query is an expression written in M
language
Query text can be edited (and copied)
#SQLSAT454
THE M LANGUAGE
#SQLSAT454
M
Functional language similar to F#
No real iterative statements
Lambda functions
Flexible, can be invoked dynamically
Expression.Evaluate()
#SQLSAT454
Expressions
An expression return a value
1 + 1 is an expression
1 + 1 is evaluated to 2 (returned value)
Values can be:
Primitive (numbers, Boolean, text)
Table, List, Record, Function (data and code!)
#SQLSAT454
Record
Defined between square brackets. Inline
fields, nesting is possible.
Evaluation of first record produces the
second record in the example.
[
Sales = [ FirstHalf = 1000, SecondHalf = 1100 ],
Total = Sales[FirstHalf] + Sales[SecondHalf]
]
[
Sales = [ FirstHalf = 1000, SecondHalf = 1100 ],
Total = 2100
]
#SQLSAT454
List
Use curly braces for both definition and
access.
[ Sales = {
[ Year = 2007,
FirstHalf = 1000,
SecondHalf = 1100,
Total = FirstHalf + SecondHalf // 2100 ],
[ FirstHalf = 1200,
SecondHalf = 1300,
Total = FirstHalf + SecondHalf // 2500 ]
},
TotalSales = Sales{0}[Total] + Sales{1}[Total] // 4600
]
#SQLSAT454
Positional Access
Curly braces access to position in a list (0-
based index).
Same access for a row in a table.
Customer{0} // First row in Customer
Customer{1} // Second row in Customer
Customer{0}[Name] // Value of Name column in first Customer
#SQLSAT454
Function
Syntax is lambda-expression
(x) => x + 1 // Add 1
(x, y) => x + y // Sum two values
[
Add = (x, y) => x + y,
OnePlusOne = Add(1, 1), // 2
OnePlusTwo = Add(1, 2) // 3
]
#SQLSAT454
Error Handling
Syntax try / otherwise.
let
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
DuplicatedColumn = Table.DuplicateColumn(Source, "Value", "Copy of Value"),
RemovedColumns = Table.RemoveColumns(DuplicatedColumn,{"Copy of Value"}),
InsertedCustom = Table.AddColumn(RemovedColumns, "Custom",
each try Number.From( [Value] )
otherwise null ),
ChangedType = Table.TransformColumnTypes(InsertedCustom,
{{"Custom", type number}})
in
ChangedType
#SQLSAT454
let / in
Define a set of values (let) used in an
expression that will be evaluated later (in).
let Sales2007 = [ Year = 2007,
FirstHalf = 1000,
SecondHalf = 1100,
Total = FirstHalf + SecondHalf // 2100
],
Sales2008 = [ Year = 2008,
FirstHalf = 1200,
SecondHalf = 1300,
Total = FirstHalf + SecondHalf // 2500
]
in Sales2007[Total] + Sales2008[Total] // 4600
#SQLSAT454
Query Folding
Expression evaluation can be pushed to data
source
Only certain data sources
e.g. SQL Server, OData
Only certain operations
You can stop that:
Using Table.Buffer()
Combining different data sources
Writing explicit SQL queries
#SQLSAT454
Evaluations
Dont forget to compile evaluations form here
http://speakerscore.com/sqlsat454
#SQLSAT454
#sqlsat454
THANKS!
#SQLSAT454
