Hi guys,

This week I have delivered together with Mohammad Ali, from the Power BI dataflows team, the session named "Microsoft Power BI: Enterprise-grade BI with Power BI dataflows" (aka "What's new in Power BI Dataflows"). The session video is available here. It also includes the session's slides, downloadable from here (in the page of the session video they are listed under "Other resources").

For convenience, I'm embedding the video here (in this blog post) as well:

Hi all,

The enhanced engine of the (Premium) Power BI dataflows, mentioned in the calculator at http://aka.ms/dfparams, uses M query rewrites to target the source of the entities to be the cached entities in the hosted SQL engine.

For example, if we have two entities (and their corresponding M expressions):

Name	M Expression
Orders	let     Source = OData.Feed("https://services.odata.org/V2/Northwind/Northwind.svc/", null, [Implementation = "2.0"]),     Navigation = Source{[Name = "Orders", Signature = "table"]}[Data],     Result = Table.RemoveColumns(Navigation, {"Customer", "Employee", "Order_Details", "Shipper"}) in     Result
AggregatedOrders	let     Source = Orders,     #"Grouped rows" = Table.Group(Source, {"ShipCountry"}, {{"TotalFreight", each List.Sum([Freight]), type number}}) in     #"Grouped rows"

During the refresh operation of the workflow, if the enhanced engine is not enabled, the queries are rewritten to:

Name	M Expression
Orders	let     Source = PowerBI.Dataflows([TransactionId="<transaction id>", WorkspaceId="<workspace id>", DataflowId="<dataflow id>", TenantId="<tenant id>", IsInternalRewrite=true]),     Workspace = Source{{[workspaceId="<workspace id>"]}}[Data],     Dataflow = Workspace{{[dataflowId="<dataflow id>"]}}[Data],     Entity = Dataflow{{[entity="Orders"]}}[Data] in     Entity
AggregatedOrders	<as above (unmodified)>

Essentially, the Orders table is rewritten to point to the Orders entity just processed - using, actually, the dataflow M connector (with some internal options - like, for example, the transaction id, such that the data of this current transaction be observed). Without this rewrite, the Orders table will go "through" to the original data source, re-retrieving all of the orders. This would have the following implications:

1. Performance impact - going to the original data source, and applying all the transforms up the chain, again, can be - in many cases - way more expensive than simply use the already processed result of the upstream M transforms.
2. Consistency - going, again, and reexecuting the query could, potentially, give a different result than what the immediate upstream entity has observed during its evaluation (e.g. if new rows got added/updated in the table). Observing the cached result would not have this behavior.

In the rewrite above, localhost appears as we host a SQL Server instance in the dedicated Power BI capacity (the enhanced engine is, though, not available in the A1, A2, EM1 and EM2 skus). For each workspace we have a database and for each dataflow we have a schema - as you can infer from the rewrite above.

So you see, now, that, when AggregateOrders is executed, it'll observe a SQL table in the place of its Orders reference. Therefore, the M engine will try to fold the transforms in the AggregateOrders against SQL. However, not all transforms are supported - in that case, the transform will be partially folded and the rest will be executed inside the M engine. This is still better than the case when the rewrite is used against ADLS Gen2 (because, in that case, nothing gets folded - here at least some part gets folded; also reading from SQL can be, generally, faster). As a note, the tables stored in the SQL Server instance of the enhanced engine are columnar indexes (stored in Vertipaq format).

To help you be guided through what transforms are folded and what are not, I have put here a table of transforms and their behavior (full folding/not full folding). The table is not exhaustive. The columns of the tables are:

1. Index - the current row number
2. Name - formatted as family:name. For example, Conversion:Conversion_Double_DateTime represents the case of an M transform from the data types conversion family.
3. Folds - yes (green)/no (red) - an indication if full folding happens.
4. Mashup/Sql - the M code and the total/partial SQL folding. The minimal folding is select from as SQL is the source of data in the rewrite so some query will be pushed to SQL.

One other mention: you could figure out what the capabilities of folding are if you'd build a direct query dataset (in Power BI desktop) on top of a sql table and try the dataflow dowstream transform. Each of the steps would be presented to the user if folding is successful or not (it'll ask you to change the transform or enable loading).

Finally, all foldable operations should happening earlier rather than later in the script (if a mixture of foldable/not foldable operations happen). The M engine can reorder the steps if safe - but it's certainly not always done.

The current table of (some of the) transforms and their folding is below:

Index	Name	Folds	Mashup/Sql
1	Conversion: Conversion_Double_DateTime	No	shared RESULT = let     QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type datetime}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[String] as [String],     [$Table].[Int] as [Int],     [$Table].[Double] as [Double],     [$Table].[Date] as [Date],     [$Table].[IntAsString] as [IntAsString],     [$Table].[DoubleAsString] as [DoubleAsString],     [$Table].[DateTeAsString] as [DateTeAsString] from [dbo].[Conversion] as [$Table]
2	Conversion: Conversion_Double_Int	No	shared RESULT = let     QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[String] as [String],     [_].[Int] as [Int],     [_].[Double] as [Double],     [_].[Date] as [Date],     [_].[IntAsString] as [IntAsString],     [_].[DoubleAsString] as [DoubleAsString],     [_].[DateTeAsString] as [DateTeAsString] from [dbo].[Conversion] as [_]'
3	Conversion: Conversion_Double_String	Yes	shared RESULT = let     QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type text}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[String] as [String],     convert(nvarchar(max), [_].[Int]) as [Int],     [_].[Double] as [Double],     [_].[Date] as [Date],     [_].[IntAsString] as [IntAsString],     [_].[DoubleAsString] as [DoubleAsString],     [_].[DateTeAsString] as [DateTeAsString] from [dbo].[Conversion] as [_]'
4	Conversion: Conversion_Int_DateTime	No	shared RESULT = let     QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type datetime}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[String] as [String],     [$Table].[Int] as [Int],     [$Table].[Double] as [Double],     [$Table].[Date] as [Date],     [$Table].[IntAsString] as [IntAsString],     [$Table].[DoubleAsString] as [DoubleAsString],     [$Table].[DateTeAsString] as [DateTeAsString] from [dbo].[Conversion] as [$Table]
5	Conversion: Conversion_Int_Double	No	shared RESULT = let     QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[String] as [String],     [_].[Int] as [Int],     [_].[Double] as [Double],     [_].[Date] as [Date],     [_].[IntAsString] as [IntAsString],     [_].[DoubleAsString] as [DoubleAsString],     [_].[DateTeAsString] as [DateTeAsString] from [dbo].[Conversion] as [_]'
6	Conversion: Conversion_Int_String	Yes	shared RESULT = let     QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type text}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[String] as [String],     convert(nvarchar(max), [_].[Int]) as [Int],     [_].[Double] as [Double],     [_].[Date] as [Date],     [_].[IntAsString] as [IntAsString],     [_].[DoubleAsString] as [DoubleAsString],     [_].[DateTeAsString] as [DateTeAsString] from [dbo].[Conversion] as [_]'
7	Conversion: Conversion_String_DateTime	Yes	shared RESULT = let     QueryResult = Table.TransformColumnTypes(Conversion,{{"DateTeAsString", type datetime}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[String] as [String],     [$Table].[Int] as [Int],     [$Table].[Double] as [Double],     [$Table].[Date] as [Date],     [$Table].[IntAsString] as [IntAsString],     [$Table].[DoubleAsString] as [DoubleAsString],     [$Table].[DateTeAsString] as [DateTeAsString] from [dbo].[Conversion] as [$Table]
8	Conversion: Conversion_String_Double	Yes	shared RESULT = let     QueryResult = Table.TransformColumnTypes(Conversion,{{"DoubleAsString", type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[String] as [String],     [$Table].[Int] as [Int],     [$Table].[Double] as [Double],     [$Table].[Date] as [Date],     [$Table].[IntAsString] as [IntAsString],     [$Table].[DoubleAsString] as [DoubleAsString],     [$Table].[DateTeAsString] as [DateTeAsString] from [dbo].[Conversion] as [$Table]
9	Conversion: Conversion_String_Int	No	shared RESULT = let     QueryResult = Table.TransformColumnTypes(Conversion,{{"IntAsString", Int64.Type}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[String] as [String],     [$Table].[Int] as [Int],     [$Table].[Double] as [Double],     [$Table].[Date] as [Date],     [$Table].[IntAsString] as [IntAsString],     [$Table].[DoubleAsString] as [DoubleAsString],     [$Table].[DateTeAsString] as [DateTeAsString] from [dbo].[Conversion] as [$Table]
10	GroupBy: GroupBy_2ColumnsCountDouble	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"Age", "Country"}, {{"row_count", each Table.RowCount(_), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[Age] as [Age],     [rows].[Country] as [Country],     count(1) as [row_count] from [dbo].[Customers] as [rows] group by [Age],     [Country]'
11	GroupBy: GroupBy_2ColumnsCountInt	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"ZipCode", "Country"}, {{"row_count", each Table.RowCount(_), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],     [rows].[Country] as [Country],     count(1) as [row_count] from [dbo].[Customers] as [rows] group by [ZipCode],     [Country]'
12	GroupBy: GroupBy_AverageDouble	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"Age"}, {{"Age-Avg", each List.Average([Id]), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[Age] as [Age],     avg(convert(float, [rows].[Id])) as [Age-Avg] from [dbo].[Customers] as [rows] group by [Age]'
13	GroupBy: GroupBy_AverageInt	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"ZipCode"}, {{"ZipCode-Avg", each List.Average([ZipCode]), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],     avg(convert(float, [rows].[ZipCode])) as [ZipCode-Avg] from [dbo].[Customers] as [rows] group by [ZipCode]'
14	GroupBy: GroupBy_CountDistinctRowsDouble	Yes	shared RESULT = let Source = Customers, QueryResult = Table.Group(Source, {"Age"}, {{"Count", each Table.RowCount(Table.Distinct(Source)), type number}}) in QueryResult; select count_big(1) as [$Item] from (     select distinct [_].[Id],         [_].[Name],         [_].[Age],         [_].[ZipCode],         [_].[Country]     from [dbo].[Customers] as [_] ) as [$Table]
15	GroupBy: GroupBy_CountDistinctRowsInt	Yes	shared RESULT = let Source = Customers, QueryResult = Table.Group(Source, {"ZipCode"}, {{"Count", each Table.RowCount(Table.Distinct(Source)), type number}}) in QueryResult; select count_big(1) as [$Item] from (     select distinct [_].[Id],         [_].[Name],         [_].[Age],         [_].[ZipCode],         [_].[Country]     from [dbo].[Customers] as [_] ) as [$Table]
16	GroupBy: GroupBy_CountDistinctRowsString	Yes	shared RESULT = let     Source = Customers,     QueryResult = Table.Group(Source, {"Country"}, {{"Count", each Table.RowCount(Table.Distinct(Source)), type number}}) in     QueryResult; select count_big(1) as [$Item] from (     select distinct [_].[Id],         [_].[Name],         [_].[Age],         [_].[ZipCode],         [_].[Country]     from [dbo].[Customers] as [_] ) as [$Table]
17	GroupBy: GroupBy_CountRowsDouble	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"Age"}, {{"count-rows", each Table.RowCount(_), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[Age] as [Age],     count(1) as [count-rows] from [dbo].[Customers] as [rows] group by [Age]'
18	GroupBy: GroupBy_CountRowsInt	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"ZipCode"}, {{"count-rows", each Table.RowCount(_), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],     count(1) as [count-rows] from [dbo].[Customers] as [rows] group by [ZipCode]'
19	GroupBy: GroupBy_CountRowsString	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"Country"}, {{"count-rows", each Table.RowCount(_), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[Country] as [Country],     count(1) as [count-rows] from [dbo].[Customers] as [rows] group by [Country]'
20	GroupBy: GroupBy_MaxDouble	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"Age"}, {{"Age-Max", each List.Max([Age]), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[Age] as [Age],     max([rows].[Age]) as [Age-Max] from [dbo].[Customers] as [rows] group by [Age]'
21	GroupBy: GroupBy_MaxInt	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"ZipCode"}, {{"ZipCode-Max", each List.Max([ZipCode]), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],     max([rows].[ZipCode]) as [ZipCode-Max] from [dbo].[Customers] as [rows] group by [ZipCode]'
22	GroupBy: GroupBy_MedianDouble	No	shared RESULT = let     QueryResult = Table.Group(Customers, {"Age"}, {{"Age-Median", each List.Median([Age]), type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
23	GroupBy: GroupBy_MedianInt	No	shared RESULT = let     QueryResult = Table.Group(Customers, {"ZipCode"}, {{"ZipCode-Median", each List.Median([ZipCode]), type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
24	GroupBy: GroupBy_MinDouble	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"Age"}, {{"Age-Min", each List.Min([Age]), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[Age] as [Age],     min([rows].[Age]) as [Age-Min] from [dbo].[Customers] as [rows] group by [Age]'
25	GroupBy: GroupBy_MinInt	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"ZipCode"}, {{"ZipCode-Min", each List.Min([ZipCode]), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],     min([rows].[ZipCode]) as [ZipCode-Min] from [dbo].[Customers] as [rows] group by [ZipCode]'
26	GroupBy: GroupBy_SumDouble	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"Age"}, {{"Age-Sum", each List.Sum([Age]), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[Age] as [Age],     sum([rows].[Age]) as [Age-Sum] from [dbo].[Customers] as [rows] group by [Age]'
27	GroupBy: GroupBy_SumInt	Yes	shared RESULT = let     QueryResult = Table.Group(Customers, {"ZipCode"}, {{"ZipCode-Sum", each List.Sum([ZipCode]), type number}}) in     QueryResult; execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],     sum([rows].[ZipCode]) as [ZipCode-Sum] from [dbo].[Customers] as [rows] group by [ZipCode]'
28	Join: Join_FullOuter	Yes	shared RESULT = let         CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.FullOuter) in         CustomerJoinOrder; execute sp_executesql N'select [$Outer].[Id],     [$Outer].[Name],     [$Outer].[Age],     [$Outer].[ZipCode],     [$Outer].[Country],     [$Inner].[OrderId],     [$Inner].[CustomerId],     [$Inner].[OrderDate],     [$Inner].[Amount] from [dbo].[Customers] as [$Outer] full outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'
29	Join: Join_Inner	Yes	shared RESULT = let         CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.Inner) in         CustomerJoinOrder; execute sp_executesql N'select [$Outer].[Id],     [$Outer].[Name],     [$Outer].[Age],     [$Outer].[ZipCode],     [$Outer].[Country],     [$Inner].[OrderId],     [$Inner].[CustomerId],     [$Inner].[OrderDate],     [$Inner].[Amount] from [dbo].[Customers] as [$Outer] inner join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'
30	Join: Join_LeftAnti	No	shared RESULT = let         CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.LeftAnti) in         CustomerJoinOrder; select [$Table].[OrderId] as [OrderId],     [$Table].[CustomerId] as [CustomerId],     [$Table].[OrderDate] as [OrderDate],     [$Table].[Amount] as [Amount] from [dbo].[Orders] as [$Table]
31	Join: Join_LeftOuter	Yes	shared RESULT = let         CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.LeftOuter) in         CustomerJoinOrder; execute sp_executesql N'select [$Outer].[Id],     [$Outer].[Name],     [$Outer].[Age],     [$Outer].[ZipCode],     [$Outer].[Country],     [$Inner].[OrderId],     [$Inner].[CustomerId],     [$Inner].[OrderDate],     [$Inner].[Amount] from [dbo].[Customers] as [$Outer] left outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'
32	Join: Join_RightAnti	No	shared RESULT = let         CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.RightAnti) in         CustomerJoinOrder; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
33	Join: Join_RightOuter	Yes	shared RESULT = let         CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.RightOuter) in         CustomerJoinOrder; execute sp_executesql N'select [$Outer].[Id],     [$Outer].[Name],     [$Outer].[Age],     [$Outer].[ZipCode],     [$Outer].[Country],     [$Inner].[OrderId],     [$Inner].[CustomerId],     [$Inner].[OrderDate],     [$Inner].[Amount] from [dbo].[Customers] as [$Outer] right outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'
34	ManageColumns: ManageColumns_AddColumn	Yes	shared RESULT = let     QueryResult = Table.AddColumn(Customers, "Custom", each if [Name] = "a" then "x" else if [Name] = "b" then "y" else if [Name] = "c" then "z" else null, type text) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country],     case         when [_].[Name] = ''a'' and [_].[Name] is not null         then ''x''         when [_].[Name] = ''b'' and [_].[Name] is not null         then ''y''         when [_].[Name] = ''c'' and [_].[Name] is not null         then ''z''         else null     end as [Custom] from [dbo].[Customers] as [_]'
35	ManageColumns: ManageColumns_DuplicateColumns	Yes	shared RESULT = let     Cust = Customers,     QueryResult = Table.DuplicateColumn(Cust, "Id", "Id-Copy") in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country],     [_].[Id] as [Id-Copy] from [dbo].[Customers] as [_]'
36	ManageColumns: ManageColumns_RenameColumns	Yes	shared RESULT = let     QueryResult = Table.RenameColumns(Customers, {"Id", "CustId"}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [CustId],     [_].[Name] as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
37	ManageColumns: ManageColumns_ReorderColumns	Yes	shared RESULT = let     QueryResult = Table.ReorderColumns(Customers, {"ZipCode", "Age", "Id"}) in     QueryResult; execute sp_executesql N'select [ZipCode],     [Name],     [Age],     [Id],     [Country] from [dbo].[Customers] as [$Table]'
38	ManageColumns: ManageColumns_SelectColumns	Yes	shared RESULT = let QueryResult = Table.SelectColumns(Customers, {"Id"}) in     QueryResult; execute sp_executesql N'select [Id] from [dbo].[Customers] as [$Table]'
39	ManageColumns: ManageColumns_SplitColumn_ByDelimiter	No	shared RESULT = let     QueryResult = Table.SplitColumn(Customers, "Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Name.1", "Name.2"}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
40	ManageColumns: ManageColumns_SplitColumn_ByNumCharacters	No	shared RESULT = let     QueryResult = Table.SplitColumn(Customers, "Name", Splitter.SplitTextByRepeatedLengths(1), {"Name.1", "Name.2"}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
41	ManageColumns: ManageColumns_UnpivotColumns	Yes	shared RESULT = let     QueryResult = Table.UnpivotOtherColumns(Customers, {"Id", "Name", "Age", "ZipCode"}, "Attribute", "Value") in     QueryResult; execute sp_executesql N'select [$Table].[Id],     [$Table].[Name],     [$Table].[Age],     [$Table].[ZipCode],     [$Table].[Attribute],     [$Table].[Value] from [dbo].[Customers] [$Pivot] unpivot ([Value] for [Attribute] in ([Country])) as [$Table]'
42	NestedJoin: NestedJoin_FullOuter	Yes	shared RESULT = let     JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.FullOuter),     QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"}) in     QueryResult; execute sp_executesql N'select [$Outer].[Id] as [Id],     [$Outer].[Name] as [Name],     [$Outer].[Age] as [Age],     [$Outer].[ZipCode] as [ZipCode],     [$Outer].[Country] as [Country],     [$Inner].[OrderId] as [C2.OrderId],     [$Inner].[OrderDate] as [C2.OrderDate] from [dbo].[Customers] as [$Outer] full outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'
43	NestedJoin: NestedJoin_Inner	Yes	shared RESULT = let     JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.Inner),     QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"}) in     QueryResult; execute sp_executesql N'select [$Outer].[Id] as [Id],     [$Outer].[Name] as [Name],     [$Outer].[Age] as [Age],     [$Outer].[ZipCode] as [ZipCode],     [$Outer].[Country] as [Country],     [$Inner].[OrderId] as [C2.OrderId],     [$Inner].[OrderDate] as [C2.OrderDate] from [dbo].[Customers] as [$Outer] inner join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'
44	NestedJoin: NestedJoin_LeftAnti	No	shared RESULT = let     JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.LeftAnti),     QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"}) in     QueryResult; select [$Table].[OrderId] as [OrderId],     [$Table].[CustomerId] as [CustomerId],     [$Table].[OrderDate] as [OrderDate],     [$Table].[Amount] as [Amount] from [dbo].[Orders] as [$Table]
45	NestedJoin: NestedJoin_LeftOuter	Yes	shared RESULT = let     JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.LeftOuter),     QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"}) in     QueryResult; execute sp_executesql N'select [$Outer].[Id] as [Id],     [$Outer].[Name] as [Name],     [$Outer].[Age] as [Age],     [$Outer].[ZipCode] as [ZipCode],     [$Outer].[Country] as [Country],     [$Inner].[OrderId] as [C2.OrderId],     [$Inner].[OrderDate] as [C2.OrderDate] from [dbo].[Customers] as [$Outer] left outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'
46	NestedJoin: NestedJoin_RightAnti	No	shared RESULT = let     JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.RightAnti),     QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
47	NestedJoin: NestedJoin_RightOuter	Yes	shared RESULT = let     JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.RightOuter),     QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"}) in     QueryResult; execute sp_executesql N'select [$Outer].[Id] as [Id],     [$Outer].[Name] as [Name],     [$Outer].[Age] as [Age],     [$Outer].[ZipCode] as [ZipCode],     [$Outer].[Country] as [Country],     [$Inner].[OrderId] as [C2.OrderId],     [$Inner].[OrderDate] as [C2.OrderDate] from [dbo].[Customers] as [$Outer] right outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'
48	Other: ListDistinct	Yes	shared RESULT = let Source = Customers, DistinctCustomers = List.Distinct(Source[Name]) in DistinctCustomers; select distinct [Name] from [dbo].[Customers] as [$Table]
49	RowManipulation: RowManipulation_Filter	Yes	shared RESULT = let QueryResult = Table.SelectRows(Customers, each not Text.Contains([Name], "42")) in QueryResult; execute sp_executesql N'select [_].[Id],     [_].[Name],     [_].[Age],     [_].[ZipCode],     [_].[Country] from [dbo].[Customers] as [_] where not ([_].[Name] like ''%42%'')'
50	RowManipulation: RowManipulation_FilterString	Yes	shared RESULT = let QueryResult = Table.SelectRows(Customers, each not Text.Contains([Name], "42")) in QueryResult; execute sp_executesql N'select [_].[Id],     [_].[Name],     [_].[Age],     [_].[ZipCode],     [_].[Country] from [dbo].[Customers] as [_] where not ([_].[Name] like ''%42%'')'
51	RowManipulation: RowManipulation_KeepBottomRows	No	shared RESULT = let QueryResult = Table.LastN(Customers, 42) in QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
52	RowManipulation: RowManipulation_KeepDuplicates	Yes	shared RESULT = let QueryResult = let columnNames = {"Country"}, addCount = Table.Group(Customers, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(Customers, columnNames, removeCount, columnNames, JoinKind.Inner) in QueryResult; execute sp_executesql N'select [$Outer].[Id],     [$Outer].[Name],     [$Outer].[Age],     [$Outer].[ZipCode],     [$Inner].[Country] from [dbo].[Customers] as [$Outer] inner join (     select [_].[Country]     from     (         select [rows].[Country] as [Country],             count(1) as [Count]         from [dbo].[Customers] as [rows]         group by [Country]     ) as [_]     where [_].[Count] > 1 ) as [$Inner] on ([$Outer].[Country] = [$Inner].[Country] or [$Outer].[Country] is null and [$Inner].[Country] is null)'
53	RowManipulation: RowManipulation_KeepErrors	No	shared RESULT = let QueryResult = Table.SelectRowsWithErrors(Customers, {"Name"}) in QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
54	RowManipulation: RowManipulation_KeepRangeRows	No	shared RESULT = let QueryResult = Table.Range(Customers, 10, 42) in QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
55	RowManipulation: RowManipulation_KeepTopRows	Yes	shared RESULT = let QueryResult = Table.FirstN(Customers, 42) in QueryResult; execute sp_executesql N'select top 42     [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]'
56	RowManipulation: RowManipulation_RemoveAlternateRows	No	shared RESULT = let QueryResult = Table.AlternateRows(Customers, 1, 1, 2) in QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
57	RowManipulation: RowManipulation_RemoveBlankQueries	No	shared RESULT = let QueryResult = Table.SelectRows(Customers, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))) in QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
58	RowManipulation: RowManipulation_RemoveBottomRows	No	shared RESULT = let QueryResult = Table.RemoveLastN(Customers, 42) in QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
59	RowManipulation: RowManipulation_RemoveDuplicates	No	shared RESULT = let QueryResult = Table.Distinct(Customers, {"ZipCode"}) in QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
60	RowManipulation: RowManipulation_RemoveEmpty	Yes	shared RESULT = let QueryResult = Table.SelectRows(Customers, each [Name] <> null and [Name] <> "") in QueryResult; execute sp_executesql N'select [_].[Id],     [_].[Name],     [_].[Age],     [_].[ZipCode],     [_].[Country] from [dbo].[Customers] as [_] where [_].[Name] is not null and ([_].[Name] <> '''' or [_].[Name] is null)'
61	RowManipulation: RowManipulation_RemoveErrors	No	shared RESULT = let QueryResult = Table.RemoveRowsWithErrors(Customers, {"Age"}) in QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
62	RowManipulation: RowManipulation_RemoveTopRows	No	shared RESULT = let QueryResult = Table.Skip(Customers, 42) in QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
63	Sort: Sort_TableIntAsc	Yes	shared RESULT = let     QueryResult = Table.Sort(Customers,{{"ZipCode", Order.Ascending}}) in     QueryResult; execute sp_executesql N'select [_].[Id],     [_].[Name],     [_].[Age],     [_].[ZipCode],     [_].[Country] from [dbo].[Customers] as [_] order by [_].[ZipCode]'
64	Sort: Sort_TableIntDesc	Yes	shared RESULT = let     QueryResult = Table.Sort(Customers,{{"ZipCode", Order.Descending}}) in     QueryResult; execute sp_executesql N'select [_].[Id],     [_].[Name],     [_].[Age],     [_].[ZipCode],     [_].[Country] from [dbo].[Customers] as [_] order by [_].[ZipCode] desc'
65	Sort: Sort_TableMultipleAsc	Yes	shared RESULT = let     QueryResult = Table.Sort(Customers,{{"ZipCode", Order.Ascending}, {"Country", Order.Descending}}) in     QueryResult; execute sp_executesql N'select [_].[Id],     [_].[Name],     [_].[Age],     [_].[ZipCode],     [_].[Country] from [dbo].[Customers] as [_] order by [_].[ZipCode],         [_].[Country] desc'
66	Sort: Sort_TableStringAsc	Yes	shared RESULT = let     QueryResult = Table.Sort(Customers,{{"Country", Order.Ascending}}) in     QueryResult; execute sp_executesql N'select [_].[Id],     [_].[Name],     [_].[Age],     [_].[ZipCode],     [_].[Country] from [dbo].[Customers] as [_] order by [_].[Country]'
67	Sort: Sort_TableStringDesc	Yes	shared RESULT = let     QueryResult = Table.Sort(Customers,{{"Country", Order.Descending}}) in     QueryResult; execute sp_executesql N'select [_].[Id],     [_].[Name],     [_].[Age],     [_].[ZipCode],     [_].[Country] from [dbo].[Customers] as [_] order by [_].[Country] desc'
68	TableOperations: TableOperations_AppendTable	Yes	shared RESULT = let     Table1 = Table.SelectColumns(Customers, {"Id"}),     Table2 = Table.SelectColumns(Orders, {"CustomerId"}),     QueryResult = Table.Combine({Table1, Table2}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     null as [CustomerId] from (     select [Id]     from [dbo].[Customers] as [$Table] ) as [_] union all select null as [Id],     [_].[CustomerId] as [CustomerId] from (     select [CustomerId]     from [dbo].[Orders] as [$Table] ) as [_]'
69	TableOperations: TableOperations_Filldown	No	shared RESULT = let QueryResult = Table.FillDown(Customers,{"Name"}) in QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
70	TableOperations: TableOperations_Fillup	No	shared RESULT = let QueryResult = Table.FillUp(Customers,{"Name"}) in QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
71	TableOperations: TableOperations_Pivot	No	shared RESULT = let Source = Customers, QueryResult = Table.Pivot(Source, List.Distinct(Source[Name]), "Name", "Id") in QueryResult; select distinct [Name] from [dbo].[Customers] as [$Table]
72	TableOperations: TableOperations_PromoteHeader	Yes	shared RESULT = let QueryResult = Table.PromoteHeaders(Customers, [PromoteAllScalars=true]) in QueryResult; select top 1     [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
73	TableOperations: TableOperations_ReplaceErrors	No	shared RESULT = let     QueryResult = Table.ReplaceErrorValues(Customers, {{"Name", "a"}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
74	TableOperations: TableOperations_ReplaceValues_Number	Yes	shared RESULT = let     QueryResult = Table.ReplaceValue(Customers,2,7,Replacer.ReplaceValue,{"Age"}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     case         when [_].[Age] = 2 and [_].[Age] is not null         then 7         else [_].[Age]     end as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
75	TableOperations: TableOperations_ReplaceValues_String	No	shared RESULT = let     QueryResult = Table.ReplaceValue(Customers,"a","s",Replacer.ReplaceText,{"Name"}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
76	TableOperations: TableOperations_TableDistinct	Yes	shared RESULT = let Source = Customers, QueryResult = Table.Distinct(Source) in QueryResult; select distinct [_].[Id],     [_].[Name],     [_].[Age],     [_].[ZipCode],     [_].[Country] from [dbo].[Customers] as [_]
77	TableOperations: TableOperations_TableRowCount	Yes	shared RESULT = let     QueryResult = Table.RowCount(Customers) in     QueryResult; select count_big(1) as [$Item] from [dbo].[Customers] as [$Table]
78	TransformColumn: TransformColumn_Number_Abs	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Abs, type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     abs([_].[Age]) as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
79	TransformColumn: TransformColumn_Number_Combinations	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", each Number.Combinations(_, 2), type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
80	TransformColumn: TransformColumn_Number_Exp	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Exp, type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     exp([_].[Age]) as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
81	TransformColumn: TransformColumn_Number_Factorial	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Factorial, type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
82	TransformColumn: TransformColumn_Number_IntegerDivide	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"ZipCode", each Number.IntegerDivide(_, 2), type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
83	TransformColumn: TransformColumn_Number_IsEven	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.IsEven, type logical}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
84	TransformColumn: TransformColumn_Number_IsNaN	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.IsNaN, type logical}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
85	TransformColumn: TransformColumn_Number_IsOdd	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.IsOdd, type logical}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
86	TransformColumn: TransformColumn_Number_Ln	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Ln, type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
87	TransformColumn: TransformColumn_Number_Log	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"ZipCode", Number.Log, type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     [_].[Age] as [Age],     log([_].[ZipCode]) as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
88	TransformColumn: TransformColumn_Number_Log10	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"ZipCode", Number.Log10, type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     [_].[Age] as [Age],     log10([_].[ZipCode]) as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
89	TransformColumn: TransformColumn_Number_Mod	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"ZipCode", each Number.Mod(_, 2), type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     [_].[Age] as [Age],     [_].[ZipCode] % 2 as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
90	TransformColumn: TransformColumn_Number_Permutations	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", each Number.Permutations(_, 2), type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
91	TransformColumn: TransformColumn_Number_Power	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", each Number.Power(_, 2), type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     power([_].[Age], 2) as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
92	TransformColumn: TransformColumn_Number_Round	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Round, type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     round([_].[Age], 0) as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
93	TransformColumn: TransformColumn_Number_RoundAwayFromZero	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.RoundAwayFromZero, type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
94	TransformColumn: TransformColumn_Number_RoundDown	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.RoundDown, type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     floor([_].[Age]) as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
95	TransformColumn: TransformColumn_Number_RoundTowardZero	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.RoundTowardZero, type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
96	TransformColumn: TransformColumn_Number_RoundUp	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.RoundUp, type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     ceiling([_].[Age]) as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
97	TransformColumn: TransformColumn_Number_Sign	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Sign, type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
98	TransformColumn: TransformColumn_Number_Sqrt	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Sqrt, type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     sqrt([_].[Age]) as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
99	TransformColumn: TransformColumn_Text_AfterDelimiter	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.AfterDelimiter(_, "a"), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
100	TransformColumn: TransformColumn_Text_At	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.At(_, 2), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
101	TransformColumn: TransformColumn_Text_BeforeDelimiter	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.BeforeDelimiter(_, "a"), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
102	TransformColumn: TransformColumn_Text_BetweenDelimiters	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.BetweenDelimiters(_, "a", "b"), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
103	TransformColumn: TransformColumn_Text_Clean	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Clean, type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
104	TransformColumn: TransformColumn_Text_Contains	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Contains(_, "a"), type logical}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     case         when [_].[Name] like ''%a%''         then 1         else 0     end as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
105	TransformColumn: TransformColumn_Text_End	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.End(_, 2), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
106	TransformColumn: TransformColumn_Text_EndsWith	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.EndsWith(_, "a"), type logical}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     case         when right([_].[Name], len(replace(''a'', '' '', ''*''))) = ''a''         then 1         else 0     end as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
107	TransformColumn: TransformColumn_Text_From	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", Text.From, type text}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     convert(nvarchar(max), [_].[Name]) as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
108	TransformColumn: TransformColumn_Text_FromNumber	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"ZipCode", Character.FromNumber, type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
109	TransformColumn: TransformColumn_Text_FromText	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", Value.FromText, type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
110	TransformColumn: TransformColumn_Text_Insert	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Insert(_, 2, "a"), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
111	TransformColumn: TransformColumn_Text_Length	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Length, type text}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     len(replace([_].[Name], '' '', ''*'')) as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
112	TransformColumn: TransformColumn_Text_Lower	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Lower, type text}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     lower([_].[Name]) as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
113	TransformColumn: TransformColumn_Text_Middle	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Middle(_, 2, 3), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
114	TransformColumn: TransformColumn_Text_PadEnd	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.PadEnd(_, 2), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
115	TransformColumn: TransformColumn_Text_PadStart	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.PadStart(_, 2), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
116	TransformColumn: TransformColumn_Text_PositionOf	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.PositionOf(_, "a"), type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
117	TransformColumn: TransformColumn_Text_PositionOfAny	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.PositionOfAny(_, {"a"}), type number}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
118	TransformColumn: TransformColumn_Text_Proper	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Proper, type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
119	TransformColumn: TransformColumn_Text_Range	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Range(_, 2), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
120	TransformColumn: TransformColumn_Text_Remove	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Remove(_, "a"), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
121	TransformColumn: TransformColumn_Text_RemoveRange	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.RemoveRange(_, 2, 3), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
122	TransformColumn: TransformColumn_Text_Repeat	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Repeat(_, 2), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
123	TransformColumn: TransformColumn_Text_Replace	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Replace(_, "a", "b"), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
124	TransformColumn: TransformColumn_Text_ReplaceRange	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.ReplaceRange(_, 2, 3, "a"), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
125	TransformColumn: TransformColumn_Text_Reverse	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Reverse, type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
126	TransformColumn: TransformColumn_Text_Start	No	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Start(_, 2), type text}}) in     QueryResult; select [$Table].[Id] as [Id],     [$Table].[Name] as [Name],     [$Table].[Age] as [Age],     [$Table].[ZipCode] as [ZipCode],     [$Table].[Country] as [Country] from [dbo].[Customers] as [$Table]
127	TransformColumn: TransformColumn_Text_StartsWith	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.StartsWith(_, "a"), type logical}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     case         when [_].[Name] like ''a%''         then 1         else 0     end as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
128	TransformColumn: TransformColumn_Text_ToNumber	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"ZipCode", each Character.ToNumber("42"), type number}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     [_].[Name] as [Name],     [_].[Age] as [Age],     52 as [ZipCode],     [_].[Country] as [Country] from (     select [Id],         [Name],         [Age],         [Country]     from [dbo].[Customers] as [$Table] ) as [_]'
129	TransformColumn: TransformColumn_Text_Trim	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Trim, type text}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     trim(concat(nchar(32), nchar(160), nchar(5760), nchar(8192), nchar(8193), nchar(8194), nchar(8195), nchar(8196), nchar(8197), nchar(8198), nchar(8199), nchar(8200), nchar(8201), nchar(8202), nchar(8239), nchar(8287), nchar(12288), nchar(9), nchar(10), nchar(11), nchar(12), nchar(13), nchar(133), nchar(8232), nchar(8233)) from [_].[Name]) as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
130	TransformColumn: TransformColumn_Text_TrimEnd	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", Text.TrimEnd, type text}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     rtrim([_].[Name]) as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
131	TransformColumn: TransformColumn_Text_TrimStart	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", Text.TrimStart, type text}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     ltrim([_].[Name]) as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'
132	TransformColumn: TransformColumn_Text_Upper	Yes	shared RESULT = let     QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Upper, type text}}) in     QueryResult; execute sp_executesql N'select [_].[Id] as [Id],     upper([_].[Name]) as [Name],     [_].[Age] as [Age],     [_].[ZipCode] as [ZipCode],     [_].[Country] as [Country] from [dbo].[Customers] as [_]'

Hello friends,

I have not written a post in quite some time (this is the understatement of the year). This post is a calculator for the Power BI Premium Dataflows workload and a brief explanation of those parameters. The parameters are grouped in few categories:

1. Parameters that can't be changed by the user - left section in the calculator (you can change these parameter in the calculator app at the bottom of the post for educational purposes only - the value of these parameters is not in the control of the user):
• Container TTL [min] - this represents the time the M container survives (in minutes). The container might survive more, in case it's in the middle of a query. Once a query is finished, the "time bomb" is evaluated and, if exceeded, the container is disposed. The container is also disposed if it's idle and the TTL has passed (the assessment is not done only at the end of a query). The value for this parameter is 20 minutes.
• Memory Usage Factor [%] - this represents the memory usage factor for M. Out of all the memory granted by the infrastructure, the dataflows engine needs to retain a bit for its own operation (orchestration of the ETL process, transaction management etc.). Everything else (pretty much everything) is used for the actual ETL engine (M engine). The value for this parameter is 95% (i.e. only 5% is used by the dataflows engine for internal operations, 95% goes to ETL).
• CPU Usage Factor [multiplier] - the M engine has the ability to execute multiple ETL core data processes (all the transforms that lead to a dataflow entity to be created) in parallel. Moreover, each query is executed in a separate process - named mashup container (it's literally a process whose name is MashupContainer.exe - it also has nothing to do with any containerization technology, it's simply a name that uses the same term). The dataflows engine limits the number of containers of the mashup engine based on a number of criteria. Each container can run only a single query at a given moment. If the number of concurrent queries reaches the number of containers that the dataflow engine is willing to spend, queries are serialized - the number of containers represents, therefore, the degree of parallelism for the dataflow engine. One of the criteria used to limit, is the CPU available - ETL processes being a CPU "consumer", we do not want to create too many context switches as it has performance implications. To avoid unnecessary context switching, one should not have more tasks than the number of cores available. However, threads that are blocked on IO operations (e.g. reading data from the data source) provide good parallelism - as the CPU is not used during that time. To complicate matter further, it is very hard to know apriori if a query is going to use a lot of CPU (e.g. expensive in memory join or sort algorithm) or not at all (e.g. it's going to simply read the data, without any transforms, from a remote data source). Further, given that, in parallel to the dataflows engine, other workloads (e.g. Analysis Services) exist on the same dedicated capacity, this clouds the decision further. Based on experience, we set the value of the multiplier to 2. That means that, from the point of view of CPU utilization alone, we could use 2x the number of cores (e.g. the P1 sku has 4 [backend, i.e. capacity] cores - then we could have 8 containers, from the point of view of CPU restrictions). Other factors weigh in and the system will use the smallest of the computed values based on these different criteria.
• WSS 2nd Container [MB] - [wss stands for working set size] for the case when the Enhanced (Dataflows) Engine is enabled, the ETL core pipeline (per entity) is split into two parts:
• read the data and make the necessary transforms (the "ET" part of ETL) - this uses the "main" pool of containers mentioned earlier.
• write the data into SQL and underlying storage (blob or ADLS Gen2) - given that this is, simply, streaming some data, albeit using the same M engine, it does not need a lot of memory for the container, therefore, we enabled a secondary pool of containers (same number of secondary containers as are main containers) with different, much smaller, working set size. This parameter specifies the working set size for each of the secondary containers. The value is 64 MB.
• Max Cache Container [MB] - represents the maximum cache size per container in MB. Each data source can define a caching strategy such that it doesn't go over and over, across evaluations, to the data from the original location (e.g. cache an Excel file). The value of this parameter is 2048 MB.
• Max Temp Size Container [MB] - represents the maximum temporary size per container in MB. This space is used by the data firewall of the M engine (the privacy rules). When the mashup contains a combination of data sources of different privacy boundaries and the query plan cannot guarantee that the data can be flown only in the secure direction (e.g. public => private), the M engine will first cache the necessary data in this temporary space (e.g. the public data in the example just considered in this statement). The temporary space needs to be large enough to cache the entire data needed for the query that cannot, otherwise, be safely processed. The value of this parameter is currently 2048 MB.
• Sql Memory Usage Factor [%] - If the enhanced compute engine is enabled (see below), some memory has to be accounted to the SQL engine that is hosted on the dedicated capacity within the dataflow workload. The value of this parameter is 30%.
• Sql Data Streaming Enabled - inside the engine, the flow can be decomposed into reading and transforming the data and, then, stream the data to both storage (blob or ADLS Gen2) and SQL database (using the secondary containers - described above). The other alternative is to read and transform the data while writing it at the same time (in the same M query) and, then, upload the data from the blob into the SQL database. The value of this parameter is true (we chose the first strategy described here, the one that uses the secondary containers).
2. Parameters that can be changed in the UX by the administrator of the capacity in the admin portal/capacities section - middle section in the calculator. The UX looks like this:
   
   
   
• WSS Container [MB] - (labeled "Container Size (Mb)" in the UX) represents the working set size of the mashup container. The minimum value is 700 MB, and so is its default value. It is recommended to increase it to 1.5 GB-2 GB if there are complex transforms being involved. You would be able to see, using the calculator, the impact of such a choice (less parallelism - but each operation might finish earlier because more memory is assigned to each respective operation). The result of increasing the container size is less parallelism as dividing the same amount of memory to larger quantity (to get the maximum number of containers based on the memory criteria) would result in a smaller number of containers being able to be allocated while ensuring more memory for each of them. Upgrading to higher skus can allow you to increase the memory per operation while adding more parallelism (as the higher end skus have more memory). Experimentation can also help to establish which of the parameters is critical for the project (increase sku, add more memory, add parallelism etc).
• Max Memory [%] - represents the quota (out of the physical memory of the dedicated capacity) given to the dataflows workloads. As a note, the quotas of all of the workloads can be overcommited (can add up to more than 100%) - as a matter of fact, the Analysis Services (datasets) workload has always allocated 100% of the memory so, as a whole, the workloads are, indeed, overcommited. However, no workload can have by itself more than 100% of allocation. There is also an absolute minimum limit of about 1200 MB for the dataflows workload. This is not visible per se in the UX but you will get an error if you put a percentage that would end up with an absolute memory smaller than these 1200 MB. For the P1 sku, this translate to the fact that the smallest percentage you can assign is 5%.
• Enhanced Engine Enabled - (labeled "Enhanced Dataflows Compute Engine" in the UX) represents the choice of the customer to direct the dataflow workload to use and manage an internal SQL engine that caches the data in order to deliver better performance (by rewriting queries that, otherwise, would use the Azure storage and redirect them to use that cached data in SQL - but more about that in another post).
3. Parameters that depend on the hardware choice (dedicated capacity instantiated) - middle section in the calculator:
• Sku - represents a designated moniker for the capacity that was instantiated, from P1 to P5. P1-P3 are mainstream capacities that can be instantiated in every region without customer needing to contact us. P4 and P5 are super-large capacities that can be instantiated only by specific deals with Microsoft.
• Hardware Cores - not a parameter per se, it is driven by the selection of the sku. One note: the hardware cores are different than the v-cores. For example, P1 has 8 v-cores: 4 frontend, 4 backend (dedicated). These 4 backend cores are the hardware cores the calculator considers for its internal projections - this is why P1 shows 4 cores and not 8 cores (which include the 4 shared frontend cores).
• Hardware Memory - again, not a parameter per se, but a projection of the selction of the sku - it represents the size of memory of the dedicated capacity.

Today we have launched the CTP2 for Reporting Services! Please download it from here and have fun! Make sure you read the release notes!

The release contains MANY bug fixes (many customers already use this in production), performance improvements and features! The most important ones are outlined in the blog post from Friday.

Monday, the 22nd of November 2010 we will launch "PivotViewer Extension for Reporting Services" CTP2. Here is a preview taken out of the release notes:

PivotViewer Extension for Reporting Services CTP2 brings bug fixes as well as new features. This is a list of the changes:



1. Bugfix: PivotViewer Extension for Reporting Services and its sample data does not install in a subsite. The bug was fixed in CTP2. Please follow the “Migration from CTP1 to CTP2” chapter in this document or install CTP2 on a machine that never had CTP1 installed on it. Please see the forum question http://social.msdn.microsoft.com/Forums/en-CA/sqlkjpowerpivotforexcel/thread/7664690b-ea78-488f-9703-0ffd458422f0 for a discussion on this CTP1 issue.


2. Bugfix: installBIPivot.ps1 PowerShell script is not digitally signed. The bug was fixed in CTP2 (the script is now digitally signed). Please see the forum question http://social.msdn.microsoft.com/Forums/en/sqlkjpowerpivotforexcel/thread/29aa2101-5210-4a5b-81b8-d9fadef0524d for a discussion on the CTP1 issue.


3. Bugfix: PivotViewer Extension for Reporting Services does not work if the data contain null values. In CTP1 there was the workaround to change the data in order to avoid nulls (e.g. put a special value, like -1 in lieu of null). The bug was fixed in CTP2.


4. Bugfix: PivotViewer Extension for Reporting Services does not work if numeric values have too large or too small exponent. The issue was that numeric data was considered to be double. The default serialization of doubles will change to the scientific notation if the exponent is above 14 or below -4. The bug was fixed in CTP2 (by considering numeric data as decimal instead).


5. Feature: Support for arbitrary OleDb data. CTP2 supports now generic OleDb providers (e.g. Oracle or DB2) for building applications. This is in addition to the datasources supported in CTP2: PowerPivot (and Analysis Service) via Adomd.NET and SQL Server (via SqlClient). Please see more details in the “Supported Data Sources” chapter of the “PivotViewer Extension for Reporting Services.docx” document that is part of this distribution.


6. Feature: Support for links in facet data. CTP2 supports now hyperlinks in facet data. Please see more details in the “Dataset Query Specification” chapter of the “PivotViewer Extension for Reporting Services.docx” document that is part of this distribution.


7. Feature: Support for multivalued facet data. CTP2 supports now multivalued facet string data. Please see more details in the “Dataset Query Specification” chapter of the “PivotViewer Extension for Reporting Services.docx” document that is part of this distribution.

Stay tuned for when the new bits are live!!

I've produced a new video that dives down into the anatomy of a PivotViewer application for Reporting Services and shows how to build a cool app (original link: http://bit.ly/a0Rho3).



Also, there has been a bug report (thanks to Kasper) that PivotViewer Extension for Reporting Services does not deal well with null values. Indeed, this is a bug, fixed already and will be available in CTP2.

As another tip, for whenever a collection doesn't "load" Fiddler2 is your friend: it lets you investigate the web requests/responses to/from SharePoint.

Please see the video where Donald Farmer demonstrates the capabilities of PivotViewer Extension for Reporting Services. It's awsome:

I've produced a video that demonstrates how to get the PivotViewer Extension for RS installed (including the sample app) and help you get started.

Also, one quick (but important) mention (it's also part of the video): you'll have to explicitely unblock the PowerShell script in order to be able to run it (I've forgotten to sign it). The video demostrates how to unblock the script (really easy).

Here is the video. Make sure you view it in HD. Enjoy!

The CTP1 of PivotViewer for Reporting Services has just shipped! Get your free download from here. As I was saying in an earlier post, it is a concept project. It's not supported, and not a feature.

However, it is really cool! It allows you to easily create stunning visualizations on top of your BI data - and these visualizations are fully dynamic: you decide at runtime which subset of data you should see, it shows the actual live numbers, and you decide how much (meta)data you associate with each card. Also, all of the cards are generated by the service, so it is very convenient. Of course it comes with sample data, and a tutorial.

I will continue to post articles here but if you have questions, I suggest you ask them on the PowerPivot forum. I will monitor it for questions related to PivotViewer for Reporting Services.

Videos will be posted soon - I'll keep you informed (also on twitter) about the location.

Microsoft has recently released a new Silverlight control called PivotViewer. This new control helps us to make better use of the growing amounts of information around us by visualizing thousands of things at once in a way that reveals the relationships which connect them. At the heart of the PivotViewer control are “Collections”. They combine large groups of similar items, so we can begin viewing the relationships between individual pieces of information in a new way. By visualizing hidden patterns, PivotViewer enables users to discover and act on new insights.

The Business Intelligence engineering team have prototyped a new concept that couples the PivotViewer control with a utility that uses Reporting Services to automatically generate this type of collections. We showcased this concept at the BI conference in New Orleans with overwhelming support and interest from our community of BI enthusiasts and are making this demo available here to those that would like to evaluate it in their own sandbox environments.

Ted Kummert, Senior Vice President of Business Platform Division has announced the availability of this “concept project” named PivotViewer Extension for Reporting Services within the next 30 days or so. It will be just a preview, and won’t be supported product or feature of Microsoft Business Intelligence. As such, it may not work perfectly under all conditions. We look forward to your feedback and participation in this experiment and will continue working hard to bring cutting edge visualization technologies like PivotViewer to you as fast as possible. And I will maintain connection with you, the customers, via this blog, to get the feedback about this project.

We’re working out the final details for making it available. Until then, stay tuned and review the video fragments from the Teched Keynote (from offset 1h:10m or so) and from the BI Conference Keynote (from offset 1h:21m or so).