1. Table rows Partition in SQL using window functions
SELECT FirstName, LastName, City,
ROW_NUMBER() OVER ( PARTITION BY City ORDER BY FirstName ) RowNo
FROM
dbo.Customers
ORDER BY City;
Equivalent in Power Query [M-Language]
.....
Partitions = Table.Group(
#"Reordered columns",
{"FirstName", "LastName","City"}, {"Partition", each _}
)[Partition],
RowNo = List.Transform(
Partitions,
each Table.AddIndexColumn(
Table.Sort(_, {"City", "FirstName"}),
"Order Key Within Partition",
1
)
),
Result = Table.Combine(RowNo)
in
Result
.....
2. Data Type Conversion e.g convert Date [String] to Date Key [Integer] In SQL
DateKey = coalesce(cast(convert(char(8), DATE, 112) as int), 10000101)
Equivalent In Power Query [M-Language]
DateKey= Date.ToText([Date], [Format = "YYYYMMDD", Culture = "en-UK"])
3. Multiple conditional statement in SQL
Case
when [Age] between 0 and 17 then '0-17'
when [Age] between 18 and 35 then '18-35'
when [Age] between 36 and 65 then '36-65'
when [Age] between 66 and 80 then '66-80'
when [Age] > 80 then 'Over 80'
End as [Age group]
Equivalent in Power Query [M-Language]
Age Group =
if [Age] >=0 and [Age] < 18 then "0-17" else
if [Age] >= 18 and [Age] <=35 then "18-35" else
if [Age] >= 36 and [Age] <= 65 then "36-65" else
if [Age] >= 66 and [Age] <= 80 then "66-80" else
if [Age] > 80 then "Over 80"
else "Unknown"