Fill Down, Pivot Column & Custom Columns
Work with a file that mixes sales and margin data in grouped rows. Learn why Fill Down silently fails on blank text cells, how to fix it with null values, and how to pivot row values into column headers.
Real-world files often group multiple metrics — like sales and margin — under a single column, using blank rows as visual separators. The obvious fix is Fill Down, which repeats a value into the cells below it. But Power Query treats blank cells in text columns differently from truly empty ones: a blank cell is not a null, so Fill Down skips it. You have to explicitly replace blanks with null first.
This lab also introduces Pivot Column — the reverse of Unpivot. Once each row is correctly labelled (Sales or Margin), Pivot spreads those labels into their own column headers, giving you a clean side-by-side structure. You'll finish by using a Custom Column to duplicate the Date column and extract just the year from it — a foundation skill for time-based grouping in analysis.
| # | Steps | Directions |
|---|---|---|
| 1 | Create a new query for File 1D. Remove the column number reference as in Exercise 1C. | |
| 2 |
Now that we have both sales and margin data, we need to identify each row as such by filling down the grouped row headers.
|
Fill attempt
Fix — insert step before
|
| 3 | Rename the Attribute column to Date. Note: we cannot rename the Value column here — it currently holds both sales and margin data. | |
| 4 | Parse the date column and filter out the errors. | |
| 5 | Pivot the values from the Metric column into their own column headers. | |
| 6 | Change data types for all columns to whole numbers / dates / decimals / percentages as appropriate. Notice that when you change the Sales column from text to decimal, blanks become null. Empty numbers are interpreted as null by default. | |
| 7 | Add a custom column called Year that is equal to the Date column. | |
| 8 | Extract the year from the new Year column. | |
"", not a null. Fill Down only propagates into null cells — so always replace blanks with null before using Fill Down on grouped headers. The Replace Values dialog lets you leave "Value To Find" empty and type null in "Replace With".