Need partition in ranking in power query editor

[sudhav]

Hi Team,

I have a CTE in my sql which is giving ranking in a table and below is the code

select *, rank() over(partition by Empstatus,Emprole,Empdept order by empid) as Empid_Rank from EmployeeTable

now my requirement is, i wanted to create same ranking column in power query editor not in DAX.

can someone lookinto it and help me.

TIA

[Vijay_A_Verma · Accepted solution]

Insert this step where #"Changed Type" needs to be replaced with your previous step

= Table.Combine(Table.Group(#"Changed Type", {"Empstatus", "Emprole", "Empdept"}, {{"All", each Table.AddRankColumn(_, "Empid_Rank", {"Empid"}, [RankKind = RankKind.Competition])}})[All])

 Code in action here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLMS0wuySxLBTJ9E/MS01OLgCyPIKVYnWglI1R5x+LizOKSxLwSIDs4MSe1GKzIGFVRQFF+elFibi6yOSYgzZi2IMwwxW0R1AgzZCNQ7ECYYk7AOxbEeMeSsHcMDXBZhDDG0JCglwyNiPCToTH2sIOZYUKMnwxNcdoEM8cMuzVIZpgjq8DmHQtivGNJSFEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Empid = _t, Empstatus = _t, Emprole = _t, Empdept = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Empid", Int64.Type}, {"Empstatus", type text}, {"Emprole", type text}, {"Empdept", type text}}),
    #"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"Empstatus", "Emprole", "Empdept"}, {{"All", each Table.AddRankColumn(_, "Empid_Rank", {"Empid"}, [RankKind = RankKind.Competition])}})[All])
in
    #"Grouped Rows"

[sudhav]

Vijay......you are awesome man. thank you sooooooooooooooo much.. you made my day simple and happy. you saved me man. take a bow.

👏🙌