Download - Power query
#sqlsatParma
#sqlsat462November 28°, 2015
Power Query
Marco Pozzan
@marcopozzan.it
www.marcopozzan.it
#sqlsatParma
#sqlsat462November 28°, 2015
Sponsors
#sqlsatParma
#sqlsat462November 28°, 2015
Organizers
getlatestversion.it
#sqlsatParma
#sqlsat462November 28°, 2015
Marco Pozzan | @marcopozzan.it
•
•
www.beantech.it
•
• www.innovazionefvg.net
– www.marcopozzan.it
– http://www.scoop.it/u/marco-pozzan
– http://paper.li/marcopozzan/1422524394
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
•
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
•
•
•
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
–
–
–
–
–
–
–
–
–
#sqlsatParma
#sqlsat462November 28°, 2015
•
•– Power Pivot (analysis)
– Power View (reporting)
•– Query sharing with other subscribers
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
•
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
#sqlsatParma
#sqlsat462November 28°, 2015
#sqlsatParma
#sqlsat462November 28°, 2015
#sqlsatParma
#sqlsat462November 28°, 2015
#sqlsatParma
#sqlsat462November 28°, 2015
#sqlsatParma
#sqlsat462November 28°, 2015
•
• You get prompted the first time you try to connect to a data source
• Edit or remove credentials using the Data Source Settings button
•
• Prevents Power Query from sending private data to public data sources
• You get prompted the first time you join/merge the data source with another
• Levels: Public,Organizational, Private
#sqlsatParma
#sqlsat462November 28°, 2015
•– Merge two Power Query queries by matching values in columns
– Like SQL inner join and left join
•– Append output of Power Query query into another query, it combines
columns with the same name
– Like SQL Union
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
DEMO 0
•
•
• http://www.comuni-italiani.it/001/033/index.html
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
•
– There are no real looping constructs but objects like lambda functions
– is a functional language Nothing like Excel formula language or VBA
– Understanding M for writing more complex transformations and calculations
•
– Can be invoked dynamically with Evaluate
•
o Familiar, easy to remember
o Easy to read and write; limited syntax, use of non standard symbols
o A superset of the Excel formula language
o Powerful capabilities for the advanced user
#sqlsatParma
#sqlsat462November 28°, 2015
•
•– 3 + 3 is an expression
– 3 + 3 return the value 6
•– Primitives types like: Boolean , text , number, null, logical, number, text, binary, any,
none, Time, date, datetime, datetimezone, duration
– Complex types like: record, list, table, function, user defined, (data and code)!
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
[Man = [Athletic = 10 , NonAthletic = 50 ],Woman = [Athletic = 20 , NonAthletic = 40 ],Total = Man[Athletic] + Woman[Athletic]
]
= [Athletic = 10 , NonAthletic = 50]
#sqlsatParma
#sqlsat462November 28°, 2015
•
•– to a position N of a list
– a row in a table
[Data = {
[Athletic = 10 , NonAthletic = 50 ],[Athletic = 20 , NonAthletic = 40 ]
},Total = data{0}[Athletic] + data{1}[Athletic]
]
= { 1..7 }
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
=#table( {"A", "B"},{ {1, 2}, {3, 4}
} )
#sqlsatParma
#sqlsat462November 28°, 2015
•
– Result of previous step typically used in the next step
– Step can be defined and reused later on
– Step isn’t evaluated if it isn’t used
letSource = Sql.Database(".", "AdventureWorksDW2012"),Sales = Table.FirstN(Source{[Schema="dbo",Item="Sales"]}[Data],10),Rank = List.Sort(Sales[SalesAmount] ,Order.Ascending),
inRank
•
– These become the steps in a query
– Each query created by the UI consist of a let single expression
•
– This is the output of the query
– It can reference any of the expressions
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
DEMO 1
#sqlsatParma
#sqlsat462November 28°, 2015
•– A function is a value which, when invoked with arguments, produces a new value.
– Function are written by listing the function’s parameters in parentheses, followed
by the goes-to symbol =>, followed by the expression defining the function.
(x) => (x*x) //Multiply the value x to x(x,y) => (x*y) //Multiply the value x to y
[Mol = (x,y) = > x*y
]
#sqlsatParma
#sqlsat462November 28°, 2015
DEMO 2
•
•
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
DEMO 3
•
#sqlsatParma
#sqlsat462November 28°, 2015
DEMO 4
•
#sqlsatParma
#sqlsat462November 28°, 2015
•– = Table.RemoveRowsWithErrors(#"Changed Type", {"Età"})
•– Table.ExpandRecordColumn(#"Inserted Eta_Try", "Eta_Try", {"HasError", "Value",
"Error"}, {"Eta_Try.HasError", "Eta_Try.Value", "Eta_Try.Error"}),
#sqlsatParma
#sqlsat462November 28°, 2015
•o Attempts evaluation
o Encodes results and errors as record values
o Optional otherwise clause
=Table.AddColumn(Source, “Eta_Bis", each try Number.From([Età]) otherwise 0)
try error "Bad"// [ HasError = true,// Error = [// Reason = "Expression.Error",// Message = "Bad",// Detail = null// ]// ]
try error "Bad" otherwise 42 // 42
#sqlsatParma
#sqlsat462November 28°, 2015
DEMO 5
#sqlsatParma
#sqlsat462November 28°, 2015
•
– When?o Column filters, row filters, joins, group by, pivot, unpivot
o Numeric calculations, aggregations
– Functionality depends on the data sourceo Relational sources (SQL Server, Oracle, etc) support the
most functionality
o Also fold to OData, Active Directory, Exchange, and others
•o Use Table.Buffer() or List.Buffer()
o By combining data from multiple data sources with different privacy level
o Preventing query folding with custom SQL (Sql.Database("localhost","adventureworkdw",[Query="select * from dimdate"])
#sqlsatParma
#sqlsat462November 28°, 2015
•– if you provide a native SQL query this is performed directly in the source data
– that the queries are optimized (i.e. nolock ) .
– Useful when user does not want rewrite in M language an existing query
•– Query isn't parsed before sending it to the source
– Can’t guarantee how often it is executed
#sqlsatParma
#sqlsat462November 28°, 2015
DEMO 6
•
#sqlsatParma
#sqlsat462November 28°, 2015
•
– Different visibility levels: private, group, public (within the company)
•
•– In this case the query is copied and executed locally, there are not processing on the
server
#sqlsatParma
#sqlsat462November 28°, 2015
DEMO 7
•
•
#sqlsatParma
#sqlsat462November 28°, 2015
•
– Generate steps using the UI, then tweak the code
– Formatting is easier in the UI
– Some things only work through the UI (“auto steps”)
•
– Use #shared to see all exported functions (and keywords)
– Typing in the function name will display its help, and prompt for parameter
•
– Use try/catch to isolate errors
– Select and remove rows with errors
– Table.Buffer will stop folding from occurring
#sqlsatParma
#sqlsat462November 28°, 2015
•
•
•– Certify query
– Consume views
•
#sqlsatParma
#sqlsat462November 28°, 2015
http://www.microsoft.com/en-us/download/details.aspx?id=39933
http://go.microsoft.com/fwlink/?LinkID=235475
http://go.microsoft.com/fwlink/?LinkID=320634
http://go.microsoft.com/fwlink/?LinkID=398594
#sqlsatParma
#sqlsat462November 28°, 2015
Q&A
Questions?
#sqlsatParma
#sqlsat462November 28°, 2015
THANKS!
http://speakerscore.com/PowerQuery
https://app.bingpulse.com/eventspulse/sqlsaturday462
#sqlsatParma
#sqlsat462