Power Query: Fiddly Fill Down

Welcome to our Power Query blog. This week, I look at an example where using the standard fill down will not work and I have to use a conditional column to fill down instead.

I have some data on tents supplied by my imaginary company:

I would like to get this into a more standard format, by filling down the tent and awning types. I begin by extracting my data to Power Query, by using the ‘From Table’ option on the ‘Get & Transform’ section of the Data tab.

I accept the defaults and tick the box to indicate that my table does indeed have headers.

I start by filling down my Tent column by right-clicking that column and selecting ‘Fill’ and then ‘Down’.

I continue and fill in the tent types.

However, if I now do the same thing to Awning, the results are not what I want.

Awning has been filled in for those rows where only the tent type should appear. The first row featuring ‘Medium’ tent should have null in Awning, just as the first row for ‘Large’ does. I remove my ‘Filled Down’ step from ‘APPLIED STEPS’ and don’t fill down for either Tent or Awning. Instead, I start by adding a conditional column from the ‘Add Column’ tab:

I am creating a new column which will have the value in Awning if Tent is null, otherwise it will be ‘Don’t Fill’.

I try filling down again, but this time I select both Tent and Awning Rule, and then right-click and choose ‘Fill’ and then ‘Down’.

This gives me a better result:

The rows where I don’t want the Awning value have ‘Don’t Fill’ in them, which I can easily replace.

I click ‘OK’ and remove the original Awning column, moving my new Awning Rule column next to Tent and then renaming it to Awning.

Next time, I’ll look at another solution using a conditional method to replace the values instead.

Come back next time for more ways to use Power Query!