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 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

 

Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thats absolutely what I'm looking for! Thank you!

  • miguel's avatar
    miguel
    Community Admin
    3 years ago

    hi!

    Could you please share a more thorough example on what you're trying to achieve? if you can share a sample dataset and a sample output that would help us understand more what you're trying to accomplish without being directly associated with a language different than the one used by Power Query (inside of Dataflows).

     

    As a more generic answer, when it comes to doing ranks in Power Query you can use the Rank column feature as described in the article below:

    Rank column (Preview) - Power Query | Microsoft Learn

  • 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!