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 [_]' |
|
Halo,I'm Helena Julio from Ecuador,I want to talk good about Le_Meridian Funding Investors on this topic.Le_Meridian Funding Investors gives me financial support when all bank in my city turned down my request to grant me a loan of 500,000.00 USD, I tried all i could to get a loan from my banks here in Ecuador but they all turned me down because my credit was low but with god grace I came to know about Le_Meridian so I decided to give a try to apply for the loan. with God willing they grant me loan of 500,000.00 USD the loan request that my banks here in Ecuador has turned me down for, it was really awesome doing business with them and my business is going well now. Here is Le_Meridian Funding Investment Email/WhatsApp Contact if you wish to apply loan from them.Email:lfdsloans@lemeridianfds.com / lfdsloans@outlook.comWhatsApp Contact:+1-989-394-3740.
ReplyDeleteThe use and applications of Power BI are very well structured and this video perfectly demonstrates some crucial facts.
ReplyDeletePowerbi Read Rest
With the recently announced data flows feature for Power BI, you can now build cloud-based workflows that connect your favorite data sources. These data flows allow you to build complexData Processing workflows and deploy them directly to the cloud. You can then use Power BI to create visualizations and reports using the data processed by your data flows. Power BI data flows are cloud-based and are built on Microsoft Azure SQL Data Warehouse, the enterprise data warehouse service built on Microsoft Azure. Since they are cloud-based, they can run any complex SQL code, allowing you to write the logic to manipulate your data.
ReplyDeleteGreat Article!
ReplyDeleteGet in touch with a Microsoft Power BI Partner to know more about the solution and its integration.
your valuable information and time. Please keep updating.
ReplyDeletePower BI Course
Power BI Online Training