Forum Discussion

sudhav's avatar
sudhav
Helper V
3 years ago
Solved

Need partition in ranking in power query editor or Dataflows in Fabric

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 EmployeeT...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Did you ever work this out? I also need rank over partition and can't seem to find how.

 

If anyone can pick this up, an example table for me personally would be something like:

 

IDModified_DateRank
100101-01-20242
100105-01-20241
100203-01-20241
100307-01-20241

 

Output provides the latest modified from the table partitioned by ID.

 

Thanks

  • mllopis's avatar
    mllopis
    Community Admin
    2 years ago

    Hi Shadow,

     

    Rank Column transform doesn't support partitions directly, but as a workaround you can first group rows in your table by the field you want to use as a partition criteria, then rank each of the partitions, and finally expand your nested ranked tables back into a single flat table.

     

    Here's a sample M script that does this over the data example you shared:

    let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUTLUN9Q3MjAyUYrVQRIzRREzAosZo4gZg8XMoWKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ModifiedDate = _t]),
      #"Changed column type" = Table.TransformColumnTypes(Source, {{"ID", Int64.Type}, {"ModifiedDate", type date}}),
      #"Grouped rows" = Table.Group(#"Changed column type", {"ID"}, {{"Data", each _, type nullable table[ID = nullable Int64.Type, ModifiedDate = nullable date]}}),
      #"Added custom" = Table.AddColumn(#"Grouped rows", "Custom", each Table.AddRankColumn([Data], "Rank", {{"ModifiedDate", Order.Descending}})),
      #"Removed columns" = Table.RemoveColumns(#"Added custom", {"Data"}),
      #"Expanded Custom" = Table.ExpandTableColumn(#"Removed columns", "Custom", {"ID", "ModifiedDate", "Rank"}, {"ID.1", "ModifiedDate", "Rank"}),
      #"Removed columns 1" = Table.RemoveColumns(#"Expanded Custom", {"ID.1"})
    in
      #"Removed columns 1"
     
    Hope that helps!