SQL Scripts equivalent in M-Language for Power Query

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"