Unfill in Power Query

Recently I received a question on how to Unfill in Power Query.  In other words, we want the opposite of the Fill feature, which fills data into blank cells (cells that contain a null value.)  If we see repeating values, we’d like to keep only the first, then replace all subsequent duplicate values with the null keyword.

Now I’ll be honest that I’d typically never do this.  I’d load the values into a table, then use a PivotTable to show the data the way I want to see it:

A table with repeating values, and a pivottable that suppresses repeating values

But having said this, if you need to have your data look like this…

A table of Animals, Colour and Amount that shows blanks under each repeating animal

… well then why not?

Unfill data with Power Query – Step 1

The first thing we need to do is run our recipe for numbering grouped rows.  (You can find this in our Power Query Recipe Cards, or in our Power Query Academy videos.)

Namely, it looks like this:

  • Sort the data by Animal to order it
  • Group the data by Animal
    • Add a single aggregation called “Data” for All Rows
  • Go to Add Column -> Custom Column and use the following formula
    • =Table.AddIndexColumn([Data],"Row",1,1)
  • Right click the “Custom” column -> Remove Other Columns
  • Expand all columns from the Custom Column

You’ve now got your rows numbered:

A Power Query showing Animal, Amount, Colour and a Row Number where each row with the same animal has a unique value starting from one

Unfill data with Power Query – Step 2

Once you’re in this state, it actually becomes pretty easy:

  • Go to Add Column -> Custom Column and use the following formula
    • = if [Row] = 1 then [Animal] else null
  • Remove the [Animal] column and the [Row] columns
  • Re-order the columns as desired
  • Rename [Custom] to Animal
  • Set the data types

Once done, you’ll notice that we have unfilled the data nicely.

A Power Query showing Animal, Colour and Amount, but only the first instance of a Animal is shown in the Animal column with duplicates showing as null

Final Thoughts

As I mentioned at the outset, this isn’t something I ever anticipate myself doing.  But if you do have a good business use case, I’d be curious to know what it is.  (I assume the asker did – although it came from a comment on an old blog post, so haven’t been able to ask.)  Please share in the comments. ?

5 thoughts on “Unfill in Power Query

  1. Hi Ken,

    Thank you so much. That was immensely helpful.
    We are doing accounts reconciliation. We have to merge data from multiple tables, each will be downloaded on a daily basis. there are days where we have more than 1 occurrence each. I have been trying to solve this issue since November 2019.
    I can send you the file if you would like to have a look at why we need to do the above.

    Thanks again. You really are awesome!

  2. let
    Source = Excel.CurrentWorkbook(){[Name="D"]}[Content],
    mGroup = Table.Group(Source, {"Animal"}, {{"GRP", each Table.AddIndexColumn(_,"Row",1,1)}}),
    mXpandGrp = Table.ExpandTableColumn(mGroup, "GRP", {"Color", "Amount", "Row"}),
    mReplaceVal = Table.ReplaceValue(mXpandGrp,each [Animal],each if [Row] = 1 then [Animal] else null,Replacer.ReplaceValue,{"Animal"}),
    mRemCols = Table.RemoveColumns(mReplaceVal,{"Row"})
    in
    mRemCols

  3. Back when I learned to program (Cobol) we called them "Control Break Reports". It was a bother to learn all of the specifics needed to make them work properly, especially when you had more than one control break (group) level.
    .
    Another term that it would be useful to associate with this article is "UnPivot". Since that is what we normally would do in PowerQuery if we were given this sort of report as input to be used in a Pivot Table.
    .
    You don't know why someone would want to do this in PQ... I have a couple of speculations:
    .
    Could be because they are not aware (enough) of pivot tables. As you mention, this is a natural output from a PivotTable. So the person asking for help is trying to do it "the hard way" because he is not aware there is a much easier, "better" way of doing it, in a PivotTable.
    .
    Or, he could be in the position of working for an unreasonable manager/client who absolutely insists that the new report look EXACTLY LIKE the old one (ie created in COBOL). Someone who is not willing to accept the minor differences in the look of a PivotTable. Been there, done that ...

  4. PS: In your example PivotTable you could also remove the animal subtotals, and even column grand totals to make the pivot table visually identical to the "unfill" report.

  5. I think each user has a different use case for this kind of thing. The original poster sent me their workbook and - at the end of the day - they were able to satisfy their requirements using this approach. I may have done it differently, but hey... we get paid for results. 🙂

    Appreciate the Cobol info. Nothing like putting in extra effort in modern tools to generate antiquated output formats. LOL!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.