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 [_]' |
|
Hi,
ReplyDeleteI 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
@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.
ReplyDeleteI just installed the tool and the sample with no issues.
ReplyDeleteLooks 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
@Surush: Thank you Surush, maybe there is an issue with your machine? Here are the original links:
ReplyDeletehttp://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
While installing pivotviewer i got the error message "permission denied on database master"...
ReplyDeletewhich 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
Hi,
ReplyDeleteHave 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
I have the same issue and same error.
ReplyDeleteError is Unsupported data format : application/vnd.ms-excel.12
Please help!!!
peter
I have the same error as well using SQL 2012.
ReplyDeleteCristian, any suggestions or solutions?
Any help would be great!
vlc media player crack
ReplyDeleteyoutube by click crack
atom rpg trudograd download
no mans sky pc download
endnote x8 mac crack
avast premier activation crack
ammyy admin keygen crack
windows 11 pro product key