Search code examples
azureazure-synapseazure-data-factory

Data Flow - Window Transformation - NTILE Expression

I'm attempting to assign quartiles to a numeric source data range as it transits a data flow.

I gather that this can be accomplished by using the ntile expression within a window transform.

I'm failing in my attempt to use the documentation provided here to get any success.

This is just a basic attempt to understand the implementation before using it for real application. I have a numeric value in my source dataset, and I want the values within the range to be spread across 4 buckets and defined as such.

Thanks in advance for any assistance with this.

Solution

  • In Window transformation of Data Flow, we can configure the settings keeping the source data numeric column in “Sort” tab as shown below:

    enter image description here

    Next in Window columns tab, create a new column and write expression as “nTile(4)” in order to create 4 buckets:

    enter image description here

    In the Data Preview we can see that the data is spread across 4 Buckets:

    enter image description here