Deep Dive into Power Query Formula Language
SELECT Orders.OrderDate, Products.OrderID, Products.ProductSKU
FROM Products
INNER JOIN Orders ON Products.OrderID = Orders.OrderID
ORDER BY Products.ProductSKU
from p in Products
join o in Orders on p.OrderID equals o.OrderID
orderby p.ProductSKU
select new { o.OrderDate, p.OrderID, p.ProductSKU }
Products.Join(Orders, Products,
o => o.OrderID, p => p.OrderID,
(p, o) => new { o.OrderDate, p.OrderID, p.ProductSKU }
).OrderBy( p => p.ProductSKU )
let
Joined = Table.Join( Products, "OrderID", Orders, "OrderID" ),
Columns = Table.SelectColumns(Joined, {"OrderDate", "OrderID", "ProductSKU"}),
Sorted = Table.Sort( Columns, "ProductSKU" ),
in
Sorted
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
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
http://go.microsoft.com/fwlink/?LinkID=320634
let
Source = Web.Page(Web.Contents("http://www.bing.com/blogs/site_blogs/b/search/archive/2013/12/01/eoy.aspx")),
WebTable = Source{index}[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
Sql.Database(server as text, database as text, optional options as nullable record) as table
MyCoolFunction = (index as number, category as text) as table
Date.StartOfDay(dateTime as any) as any
Value.Type( { "1", 1 } )
// type list
{ "1", 1 } is Phrases
// true
Phrases = type { text }
Person = type [ Name = text, Age = number ]
( { "1", 1 } as Phrases ) {0}
// "1"
( { "1", 1 } as Phrases ) {1}
// 1
Persons = type table Person
MakePerson = type function ( name as text, optional age as number ) as Person
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
List
{ 1, 2, 3, "hello, world" }
Record
Table
[ a = 1, b = { 1, 2, 3 }, C2 = true ]
#table(
{"n", "n^2"}, {
{1, 2},
{2, 4}
})
MyFunction = ( x, y, optional z ) =>
if z = null then
x + y
else
(x + y) / z
Function
Type
type table [ n = number, #"n^2" = number ]
Binary
#binary({0x68, 0x65, 0x6C, 0x6C, 0x6F})
Table.SelectRows( table, (r) => r[Manager] = r[Buddy] )
Table.SelectRows( table, each _[Manager] = _[Buddy] )
Table.SelectRows( table, each [Manager] = [Buddy] )
if 1 < 2 then "hurray" else "sad face"
let x = Number.Atan(3) in x * x
error "Here I go wrong"
error [ Reason = "Expression.Error",
Message = "'t' should be positive",
Detail = t ]
try error "Bad"
// [ HasError = true,
//
Error = [
//
Reason = "Expression.Error",
//
Message = "Bad",
//
Detail = null
//
]
// ]
try error "Bad" otherwise 42
// 42
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
Session code
Title
Time
[DBI-B211]
All You Need to Know about Microsoft Power BI
for Office 365
Monday May 12th 1.15 PM-2.30 PM
[DBI-B324]
What’s new in Power Query for Excel
Monday May 12th 3.15-4.30 PM
[DBI-B320]
Interactive Data Visualization with Power View
Tuesday May 13th 8.30-9.45 AM
[DBI-B318]
Amazing Data Storytelling with Microsoft Power
BI Q&A (Formerly InfoNavigator)
Tuesday May 13th 10.15-11.30AM
[DBI-B212]
BI Power Hour
Tuesday May 13th 5PM-6.15 PM
[DBI-B323]
Power Query in Modern Corporate BI
Wednesday May 14th 3.15PM-4.30 PM
[DBI-B321]
Power View with Analysis Services
Multidimensional Models
Thursday May 15th 8.30-9.45 AM
http://www.trySQLSever.com
http://www.powerbi.com
http://microsoft.com/bigdata
http://channel9.msdn.com/Events/TechEd
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn
© Copyright 2024