Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table of records that contains date and time of record creations.
I wanted to rank records by date and time.
I used the Power Query AddRankColumn.
It worked fine in PowerBI Desktop.
However I soon understood that I wasn't getting the expected ranking in PowerBI Service because I implemented Incremental Refresh on my dataset.
Basically what I observed was that records were Ranked within each partitions created by PowerBI Service.... Leading to completely useless and inaccurate my rank column.
Record Creation Date | Value | Partitions in Tabular Editor | Rank column value |
June 14, 2023 | We don't care | 2023Q306 | 2 |
June 20, 2023 | We don't care | 2023Q306 | 1 |
July 3, 2023 | We don't care | 2023Q307 | 3 |
July 5, 2023 | We don't care | 2023Q307 | 2 |
July 21, 2023 | We don't care | 2023Q307 | 1 |
August 9, 2023 | We don't care | 2023Q30809 | 1 |
August 10, 2023 | We don't care | 2023Q30810 | 1 |
August 11, 2023 | We don't care | 2023Q30811 | 1 |
How would you suggest to ensure the ranking applies across partitions?
The reason I am doing that initiallly, is that I wanted to filter the report with the last 2 or 3 extractions, so selecting rank 2 or 3, shall have addressed that.
NB: the source are files, auto extracted.
Update: I have actually used the DAX Rank function instead, which gives me what I want....
nevertheless, the partionning impact on the PowerQUery is still interesting to be aware off.