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!

Get Microsoft Silverlight
9

View comments

  1. Hi,

    I believe a lot in the potential of Microsoft Labs Pivot in BI scenarios.

    One limitation is the impossibility of "changing" view (ie analysing top seller by region, channel, eccetera), which is precisely what most people do with Excel pivots, and what you cannot do with MS Labs Pivot, since every item has one data instance.

    I tried to solve this by "swapping" pivot views. http://www.logeeka.com/SunglassPivot.

    What still bothers me is the fact that Pivot can "count" but cannot "sum". It shows you the 10 top sellers, but does not allow you to sum up the total sales of those 10 top sellers.

    Any idea of how to solve this?

    Thanks so much

    fabio annovazzi

    fabio.annovazzi at gmail.com

    ReplyDelete
  2. @fabio: in pivotviewer extension for SSRS the data associated with each item is dynamic. More such "dynamicity" will be added - like, for example, the capability to cross drill to another view. It is true it "does not sum". PivotViewer is not a replacement for pivot tables of excel - more of a complementary way of visualization.

    ReplyDelete
  3. I just installed the tool and the sample with no issues.
    Looks great, congratulations Christian:)

    By the way, yesterday, I was able to see the video.
    Video is missing now (only dark screen box)!?

    Surush Cyrus
    scyrus@live.com

    ReplyDelete
  4. @Surush: Thank you Surush, maybe there is an issue with your machine? Here are the original links:
    http://www.microsoft.com/showcase/en/us/details/cc397c17-fe21-4dc0-a408-249dbb3299ae
    and
    http://www.microsoft.com/showcase/en/us/details/5d2de470-3602-4c68-a90f-7d590e05fb73

    ReplyDelete
  5. While installing pivotviewer i got the error message "permission denied on database master"...
    which user want access to database master? the user how calling the exe?
    i think it's the the step to create the database for pivotviewer

    ReplyDelete
  6. Hi,

    Have you had any joy setting this up for SQL 2012 RC0 yet?

    If so what are the neccessary tricks – I manage to install the sample but I get an error when i try to crawl the Inventory. Error is Unsupported data format : application/vnd.ms-excel.12

    Any help would be greatly appreciated

    ReplyDelete
  7. I have the same issue and same error.

    Error is Unsupported data format : application/vnd.ms-excel.12

    Please help!!!

    peter

    ReplyDelete
  8. I have the same error as well using SQL 2012.

    Cristian, any suggestions or solutions?

    Any help would be great!

    ReplyDelete
  1. 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:

    5

    View comments

  2. 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.
    Such rewrites are available only in Power BI Premium not in Power BI shared.

    For data ingestion, the (somewhat contradictory) recommendations are:
    1. Avoid complex transforms - especially against SaaS services (like Salesforce or Dynamics). Get the data in ADLS Gen 2 as fast as you can (usually the equivalent of a "select *" against the data source.
    2. However, if certain filters or operations would significantly reduce the volume of data from the source to the destination entity, you should consider pushing these operations in the import transform. M will try to translate those operations as operations against the data source. However, M is not always able to translate all of the M query to the underlying dialect of the data source (such a push/translation of the M script to the underlying language is called "folding"). In order to guarantee folding, one should/could use the native query operation when designing the entity in Power Query Online.
    When to chose the "select *" and when to chose to merge some operations in the import flow (using, potentially, the native query capability) is a bit of a conundrum whose solution (for best results) depends on the actual problem at hand.

    Now, with the enhanced engine, the folding discussion applies to downstream entities as well. [Such discussion is not relevant for downstream entities in the absence of the enhanced engine because the physical support - aka data source - in that case is an ADLS Gen2 file and nothing can be "pushed/folded" against the file as a file is not a service that can process any query]

    For the enhanced engine, the rewrite is:

    Name M Expression
    Orders let
        Source = Sql.Database("localhost", "db$<workspace id>", [CommandTimeout=#duration(1,0,0,0)]),
        Entity = Source{[Schema="schema$<dataflow id>", Item="Orders"]}[Data]
    in
        Entity
    AggregatedOrders <as above (unmodified)>

    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 [_]'

    2

    View comments

  3. 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.
    The results are computed and presented in the right section of the calculator:
    1.  Max Containers [#] - represents the max number of M containers (and, therefore, ETL dop). This is calculated once the memory and CPU constraints are taken into account. This number is for the main container pool (not related to the secondary container pool described above).
    2. Container Max WSS [MB] - represents the actual maximum working set size for each of the main containers.
    3. Has Secondary Containers - represents an indication if the engine will use a secondary pool of M container. This pool (if used) will have less memory per container (equal to WSS 2nd Container [MB] parameter) and the number of containers will be equal to the number of containers in the main pool.

    The calculator is embedded in the page below for your convenience (direct link is here):


    5

    View comments

  4. 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.
    14

    View comments

  5. 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!!
    3

    View comments

  6. 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).

    Get Microsoft Silverlight

    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.
    4

    View comments

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

    Get Microsoft Silverlight
    4

    View comments

  8. 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!

    Get Microsoft Silverlight
    9

    View comments

    1. Hi,

      I believe a lot in the potential of Microsoft Labs Pivot in BI scenarios.

      One limitation is the impossibility of "changing" view (ie analysing top seller by region, channel, eccetera), which is precisely what most people do with Excel pivots, and what you cannot do with MS Labs Pivot, since every item has one data instance.

      I tried to solve this by "swapping" pivot views. http://www.logeeka.com/SunglassPivot.

      What still bothers me is the fact that Pivot can "count" but cannot "sum". It shows you the 10 top sellers, but does not allow you to sum up the total sales of those 10 top sellers.

      Any idea of how to solve this?

      Thanks so much

      fabio annovazzi

      fabio.annovazzi at gmail.com

      ReplyDelete
    2. @fabio: in pivotviewer extension for SSRS the data associated with each item is dynamic. More such "dynamicity" will be added - like, for example, the capability to cross drill to another view. It is true it "does not sum". PivotViewer is not a replacement for pivot tables of excel - more of a complementary way of visualization.

      ReplyDelete
    3. I just installed the tool and the sample with no issues.
      Looks great, congratulations Christian:)

      By the way, yesterday, I was able to see the video.
      Video is missing now (only dark screen box)!?

      Surush Cyrus
      scyrus@live.com

      ReplyDelete
    4. @Surush: Thank you Surush, maybe there is an issue with your machine? Here are the original links:
      http://www.microsoft.com/showcase/en/us/details/cc397c17-fe21-4dc0-a408-249dbb3299ae
      and
      http://www.microsoft.com/showcase/en/us/details/5d2de470-3602-4c68-a90f-7d590e05fb73

      ReplyDelete
    5. While installing pivotviewer i got the error message "permission denied on database master"...
      which user want access to database master? the user how calling the exe?
      i think it's the the step to create the database for pivotviewer

      ReplyDelete
    6. Hi,

      Have you had any joy setting this up for SQL 2012 RC0 yet?

      If so what are the neccessary tricks – I manage to install the sample but I get an error when i try to crawl the Inventory. Error is Unsupported data format : application/vnd.ms-excel.12

      Any help would be greatly appreciated

      ReplyDelete
    7. I have the same issue and same error.

      Error is Unsupported data format : application/vnd.ms-excel.12

      Please help!!!

      peter

      ReplyDelete
    8. I have the same error as well using SQL 2012.

      Cristian, any suggestions or solutions?

      Any help would be great!

      ReplyDelete
  9. 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.
    9

    View comments

  10. 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).
    1

    View comments

About Me
About Me
My Photo
Seattle, WA, United States
I am a Distinguished Engineer at Microsoft, and, in particular, the Chief Architect of Microsoft Azure Data Division.
Azure Data subsumes:

1. The Intelligence Platform: Power BI, Azure Analysis Services, Azure Synapse (DataWarehouse, Spark, Azure Data Factory, Azure Data Explorer aka Kusto), Cosmos, Azure Messaging.
2. The operational databases group: SQL Server, Azure SQL DB, Cosmos DB, Azure MySQL, Azure Database for MariaDB.
3. Azure Databricks

I have been working inside Microsoft since 1997, after I have received the MSc degree in computer science from 'Politehnica' University of Bucharest. My responsibilities include technical strategy for the products of the Azure Data, technical tactical guidance for the teams of the division, and ... well ... a lot of coding. I am an author of 49 granted patents, and many more pending. I have received my PhD degree in Computer Science from 'Politehnica' University of Bucharest in 2006.
Blog Archive
My Blogroll
My Blogroll
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.