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 [_]' |
|
Where might one see a 'stunning' example of this new technology? :)
ReplyDeleteFew places:
ReplyDelete1. Check the demo at http://bit.ly/auWtBI
2. Check the TechEd keynote (from offset 1h:10m or so) at: http://bit.ly/d3zXCM
3. You can install the software, and play with the sample app provided
4. Stay tuned for more posts (one will come before you know it)
5. Follow twitter #pivotviewer
Thanks for the links. Glad to see it went from Amir's click-view demo at the BI Cust Panel last year, to shipping. Nice job! I just need to come up with a compelling use case at my company so I can put together a cool demo.
ReplyDeleteLooks cool. However in video it didnt show much about what data to use it on.
ReplyDelete- Is it only to be used with powerpivot or also directly on AS without ´powerpivot ?
- Is it for dimensions, numbers ?
@Steve:
ReplyDeleteCool! There will also be more posts on best practices etc
@Michael:
1. It also works with any Adomd.Net connection (and also with MS SQL Server data). But pay attention to delegation issues! (see the doc in the download)
2. It usually is for dimensions, but you decide what it is for. It is for a query. There will be another video and/or posting on how exactly to build a collection, what to have in mind etc.
Very cool - but do we need to have the full version of SharePoint (Enterprise) and PowerPivot installed to use this?
ReplyDelete@MAQ: I don't enforce full version of SharePoint but I haven't tested with anything else. If you manage to make it work otherwise, let me know :) About PowerPivot - technically might not be needed, although, you might need to install yourself the Adomd.Net for using against Analysis Services.
ReplyDeleteThe product (PivotViewer Extension for Reporting Services) allows you to (technically) build solutions on top of SQL or native AS (not only PowerPivot) as well as older versions of Reporting Services although testing has been done with only SQL 2008 R2 and PowerPivot installed.
Hi Cristian,
ReplyDeleteThanks for your help. I have managed to get PivotViewer Extensions for SSRS to work on SharePoint Foundation 2010 pointing to a SSAS 2008 Cube. I just followed the same instructions as you posted.
One further question though if I may. I'm building a collection for customer sales analysis. Is it possible for me to show numerics from the measures dimension alongside customers? For example [Measures].[Average Sale]? If so could you post an example of how the dataset query might look?
Many thanks,
Matt
@MAQ: Sure it is possible. Please read the chapter called "Dataset Query Specification" from the documentation. In a nutshell, you'll use two axes instead of one: whatever you were using on the axis 0 in the case of a single axis you would move on axis #1 (rows) and on axis 0 you will put Measures.[Average Sale]. Basically this has the effect of extending the "row" presented in the tuple on the current position of axis 1 with the cells for the headings in axis 0. The exact algorithm is in the chapter
ReplyDelete