This article is contributed. See the original author and article here.
“Fill down” is an operation common in data prep and data cleansing meant to solve the problem with data sets when you want to replace NULL values with the value from the previous non-NULL value in the sequence. Here is how to implement this in ADF and Synapse data flows.
Note that this operation can have negative performance implications because you must create a synthetic window across your entire data set with a “dummy” category value. Additionally, you must sort by a value to create the proper data sequence to find the previous non-NULL value.
In this case, my products data is missing the Color value in several rows. To fix this, I applied the fill down pattern in the Window transformation to fill in the NULL values:
To achieve this, I used the coalesce formula in the Window transformation:
coalesce(Color, last(Color, true()))
The key to make this work is to first set the Window “over” clause to your entire data set. If you do not already have a value that is the same across all rows, you can set a synthetic window value in a Derived Column. In my case, I set an integer value of 1 to a column called “dummy”.
Next, the Window sorting should be sorted by the column that sets the proper ordering such that the previous non-NULL value will be your fill value. In my case, I used the Surrogate Key transformation to create an incrementing key to sort by.
The final fill-down pattern looks something like this:
To make this easy to add to your data flow pipelines, I added the script-behind as a data flow snippet to our online recipes here.
I’ve also included this snippet below. This creates the synthetic category as “dummy” and sorts by a surrogate key. You can remove the surrogate key and use your own data-specific sort key. This code snippet assumes you’ve already added a Source transformation to your data flow called
source1 derive(dummy = 1) ~> DerivedColumn
DerivedColumn keyGenerate(output(sk as long),
startAt: 1L) ~> SurrogateKey
Rating2 = coalesce(Color, last(Color, true()))) ~> Window1
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.