Hi everyone! Today I will share how to fill null values with the value one row above by using Tableau Prep. I was working with the client’s data and I am looking for how to fill null values with the value above.
Let’s get started!
First I download the raw data from https://preppindata.blogspot.com/2020/08/2020-week-32.html
The raw file is on the left which in the column “Store Manager” has some blank value which we want to fill the value with the above row.
Step 1: Extract the rows with the manager’s name
1.1 Remove the NULL value in “Store Manager” column
1.2 Keep only “Row ID” and “Store Manager” column
Table from following Step 1
Step 2: from the data in step 1 join it back in the original table by using column Row ID
Change the join clause to >=. The objective of this step is to fill The NULL column with “Store Manager Value” on the rows below.
Step 3: Data preparation step by using FIXED LOD and Filtering the value
3.1 Creating a FIXED LOD to fix the incorrect repeated name.
FIXED LOD on the ROW ID-1 and group by Row ID and using MAX Row ID-1
3.2 Filter Calculation = Row ID-1 (Calculation Column is the FIXED LOD from step 3.1)
Step 4: Clean up the data to look like the output
Remove unrelated columns
Complete Tableau Prep Workflow
Download finish Tableau Prep Workflow from link https://drive.google.com/file/d/1L55czj5B3suX65ZdQWYnPI7MZT0y5FU8/view?usp=share_link
That’s it for this blog.