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 [_]' |
|
Love the UI in the actual Power BI "calculator". Can we get a copy of the underlying .pbix, Cristian?
ReplyDelete
ReplyDeleteEnjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles Power Bi course
Thank you so much for providing information about Power BI and some complex tools which always help a lot in learning more about how one can categorically break down complex IT problems.
ReplyDeletePowerbi Read Soap
Thanks for your information. very good article.
ReplyDeletePower BI Course
Power BI Online Training
JSM - Casino Review - JtmHub
ReplyDeleteJSM is 충청북도 출장마사지 a brand new casino offering new players 논산 출장안마 a secure environment, a fantastic variety of games 의왕 출장샵 and 광양 출장안마 great support 춘천 출장샵 from industry veterans. We've