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

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

sudhav — Thu, 03 Aug 2023 08:17:20 GMT

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

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

miguel — Thu, 03 Aug 2023 08:23:48 GMT

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

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

Anonymous — Thu, 11 Jan 2024 14:43:50 GMT

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:

ID	Modified_Date	Rank
1001	01-01-2024	2
1001	05-01-2024	1
1002	03-01-2024	1
1003	07-01-2024	1

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

Thanks

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

fabricator1 — Thu, 11 Jan 2024 15:38:26 GMT

Is this what you are looking for?
Chris Webb's BI Blog: Nested Calculations In Power Query (crossjoin.co.uk)

Adding a row rank based on a different column with Power Query – data witches (data-witches.com)

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

Anonymous — Thu, 11 Jan 2024 15:39:00 GMT

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

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

mllopis — Thu, 11 Jan 2024 18:04:58 GMT

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"})

#"Removed columns 1"

Hope that helps!