Hi @Lee Gorringe?,
DENSERANK does include NULLs as values to be ranked. However, when sorted in ascending order, NULLs will always appear as the last values in your ordered sequence. If you need to exclude NULLs from your ranking column, I would recommend adding a step after the DENSERANK function that removes the rank associated with the NULL records.
For example, in the screenshot below I've used the DENSERANK function to rank the values in the "discount" column, grouped by the "transaction_date" column. You can see that the NULL values are assigned to the highest ranking within the group.
As a next step, I can use the "Edit with formula" transformation to remove the rank values for the records where the "discount" is NULL. To do this, I can enter the following formula in the transformation builder:
CASE([denserank == max(denserank), null(), denserank])This formula identifies rows where the value entered in the "denserank" column is equal to the largest value in the "denserank" column. For each of these records, the existing rank value is overwritten with NULL. For all other records, the rank value is retained.
Since my original DENSERANK function grouped my records based on "transaction_date", I will need to insert "transaction_date" in the "Group rows by" field in the transformation builder. My full step will look like the image below:
Let me know if this helps!
