Transcript PowerQuery_SQLSatMelbournex

The Power of Power Query
Matt Masson | @mattmasson
SQL Saturday #582, Melbourne
11th February 2017
Schedule Changes
Microsoft Room
Sunil Agarwal now at 09:00 instead of 16:30
Ajay Jagannathan now at 15:15 instead of 09:00
Darren Gosbell now at 16:30 instead of 15:15
Sandisk Room
Leila Etaati now at 14:05 instead of 16:30
Hamish Watson now at 16:30 instead of 14:05
Insight / Ignia Room
Rolf Tesmer & Kristina Rumpff now at 15:15 instead of 16:30
Martin Cairney now at 16:30 instead of 15:15
Housekeeping
Mobile Phones
Please set to “stun” during sessions
Evaluations
Please complete a session Evaluation to provide feedback to our wonderful
speakers!
Also complete the Event Evaluation forms – please fill them in and return them at
the end of the day
Coffee
There a Coffee cart provided by WardyIT outside the Microsoft & Sandisk rooms if
you need a caffeine hit before the next session
Housekeeping
SQL Clinic
Don’t forget to check out the SQL Clinic to talk directly to Microsoft staff and
MVP’s about your biggest pain points or suggestions for the next versions of SQL
Server
Lunchtime Sponsor Sessions
Learn more over lunch, come hear presentations from our gold sponsors
including WardyIT, SanDisk and Insight Enterprises
Sponsor Bingo
Visit the sponsors’ desks to get your Bingo Card stamped and enter the prize
draw
One product at a time …
Power Query in Excel
Power Query add-in
for Excel 2010/2013,
and the “Get &
Transform Data”
experience in Excel
2016.
Power Query in Power BI Desktop
Provides the Get Data
experience for Power
BI Desktop, providing
consistent data
access to 74+ data
sources.
Power Query in SQL Server Data Tools
Now integrated in SQL
Server Data Tools (SQL
vNext CTP) for
Analysis Services
Tabular projects,
closing the gap in the
Self-Service to BI Pro
grow up story!
Power Query Editor
What is it so Popular?
Connectivity to a wide range of data sources of various types, shapes,
and sizes;
Highly interactive and intuitive experience for rapid and iterative
construction of queries over any data source, any size.
Consistency of experience, and parity of query capabilities over all
data sources.
Joins across different data sources; ability to create custom views over
data that can then be shared with others in your organization
Power BI Desktop
Get Data
Build your queries
Model
Visualize
Publish
Analyze and discover
Define relationships and measures
Consume
Create shared dashboards
Send to PowerBI.com
Connectivity experience
Data transformations
Query folding
Data mashup
DEMO
M IN POWER QUERY
M Language Flow
Except when it doesn’t
Result of previous step typically used in the next step
Step is stored as a variable and can be reused later on
let
M has Lazy Evaluation - values are ignored if not used
Source = Web.Page(Web.Contents("http://www.bing.com/blogs/site_blogs/b/search/archive/2013/12/01/eoy.aspx")),
WebTable = Source{0}[Data],
RenamedColumns = Table.RenameColumns(WebTable,{{"Column1", "Rank"}, {"Column2", "2013"}})
in
RenamedColumns
let
Source = Sql.Database("localhost", "AdventureWorksDW2012"),
DimCat = Source{[Schema="dbo",Item="DimProductCategory"]}[Data],
DimSubCat = Source{[Schema="dbo",Item="DimProductSubcategory"]}[Data],
DimCustomer = Source{[Schema="dbo",Item="DimCustomer"]}[Data],
Joined = Table.NestedJoin(DimSubCat,{"CategoryKey"},DimCat,{"CategoryKey"},"Category",JoinKind.Inner)
in
Joined
Query Folding
Power Query pushes work back to the source system whenever it can.
let
Source = Sql.Database("localhost", "AdventureWorksDW2012"),
dbo_DimProduct = Source{[Schema="dbo",Item="DimProduct"]}[Data],
RemovedOtherColumns = Table.SelectColumns(dbo_DimProduct,{"ProductKey", "EnglishProductName"}),
RenamedColumns = Table.RenameColumns(RemovedOtherColumns,{{"EnglishProductName", "Product"}}),
FilteredRows = Table.SelectRows(RenamedColumns, each [ProductKey] < 10)
in
FilteredRows
Query Folding
let
Source = Sql.Database("localhost", "AdventureWorksDW2012"),
dbo_DimProduct = Source{[Schema="dbo",Item="DimProduct"]}[Data],
RemovedOtherColumns = Table.SelectColumns(dbo_DimProduct,{"ProductKey", "EnglishProductName"}),
RenamedColumns = Table.RenameColumns(RemovedOtherColumns,{{"EnglishProductName", "Product"}}),
FilteredRows = Table.SelectRows(RenamedColumns, each [ProductKey] < 10)
in
FilteredRows
SELECT ProductKey, EnglishProductName as [Product]
FROM
[dbo].[DimProduct]
WHERE [ProductKey] < 10
Type System
Primitive types
 any, none
 null, logical, number, text, binary
 time, date, datetime, datetimezone, duration
Complex types
 list, record, table, function
Sql.Database = (server as text, database as text, optional options as nullable record) as table
MyCoolFunction = (index as number, category as text) as nullable table
Date.StartOfDay = (dateTime as any) as any
Ascribed Types
A value's ascribed type is the type to which a value is declared to conform. When a
value is ascribed a type, only a limited conformance check occurs. M does not perform
conformance checking beyond a nullable primitive type. M program authors that
choose to ascribe values with type definitions more complex than a nullable primitivetype must ensure that such values conform to these types.
Commonly used ascribed types
 Byte.Type, Int8.Type, Int16.Type, Int32.Type, Int64.Type
 Single.Type, Double.Type, Decimal.Type, Currency.Type, Percentage.Type
 Character.Type
SIMPLE VALUE
LITERAL
Null
null
Logical
true,
Number
1,
Text
"hello, world!"
Date
#date(2013, 3, 8)
Time
#time(15, 10, 0)
DateTime
#datetime(2013, 3, 8, 15, 10, 0)
DateTimeZone
#datetimezone(2013, 3, 8, 15, 10, 0, -8, 0)
Duration
#duration(1, 13, 59, 12.34)
false
1.2,
1.2e-3,
#infinity,
#nan
Complex Values - Lists
Complex Values - Records
Complex Values - Tables
Complex Values - Other
COMPLEX VALUE
LITERAL
Function
MyFunction = ( x, y, optional z ) =>
if z = null then
x + y
else
(x + y) / z
Type
type table [ n = number, #"n^2" = number ]
Binary
#binary({0x68, 0x65, 0x6C, 0x6C, 0x6F})
Anatomy of a Table
let
Source = Excel.Workbook(File.Contents("Sales.xlsx")),
MyTable = Source{[Item="Data",Kind="Sheet"]}[Data]
in
MyTable
Anatomy of a Table
A column in the table is a list.
MyTable[ProductKey] = { 217, 231, 485, 538, 480, 528, 480, 477 }
Anatomy of a Table
A row in the table is a record.
MyTable{2} = [ ProductKey = 485, OrderQuantity = 1, UnitPrice = 21.98,
SalesAmount = 21.98, OrderDate = #date(2008, 1, 1) ]
Anatomy of a Table
A table is a list of records
MyTable = {[ProductKey = 217,…],[ProductKey = 231,…],…}
Unary Functions
Many library functions take functions as arguments
Table.SelectRows( table, (r) => r[Manager] = r[Buddy] )
Often, those parameter functions are unary
A special syntactic form helps construct unary function values
Table.SelectRows( table, each _[Manager] = _[Buddy] )
An ‘each’ expression is just shorthand for a unary function
The single parameter of an ‘each’ function is named _
For conciseness and to get close to the DAX syntax, the _ can be omitted when accessing fields or
columns
Table.SelectRows( table, each [Manager] = [Buddy] )
Starting from a Blank Query
Creating calculated columns
Writing your own functions
DEMO
Tips and Tricks - Leverage the UI
Generate steps using the UI, then tweak the code
Some things only work through the UI (“auto steps”)
Tips and Tricks - Library Functions
Use #shared to see all exported functions (and keywords)
Typing in the function name will display its help and prompt for parameter
Tips and Tricks - Troubleshooting
Use try/otherwise to isolate errors
Select and remove rows with errors
Table.Buffer will stop folding from occurring
References
Power Query Library Function Reference on MSDN
https://msdn.microsoft.com/en-US/library/mt253322.aspx
Power Query Language Specification
https://msdn.microsoft.com/en-us/library/mt211003.aspx
Sponsors
Please make sure you visit our fantastic sponsors to get your card stamped to be in the running for a raffle prize:
How did we do?
Please complete an Evaluation to provide feedback to our wonderful speakers!
SQL Clinic
Don’t forget to check out the SQL Clinic to talk directly to Microsoft staff and MVP’s about your
biggest pain points or suggestions for the next versions of SQL Server
Lunchtime Sponsor Sessions
Learn more over lunch, come hear presentations from our gold sponsors including WardyIT, SanDisk
and Insight Enterprises
Evaluations
Also complete the Event Evaluation forms – please fill them in and return them at
day
the end of the