How to Fill Across Blank Cells in Rows with Power Query

The Fill function in Power Query (Get & Transform) can be used to copy values down or up over Null (blank) cells.  Checkout my article on how to fill down blank cells with Power Query for more on that technique.

We can also fill across rows horizontally by first Transposing the table.  Here is a screencast that shows how to fill across.

Fill Across Blank Rows in Power Query

Here are the instructions to fill across rows with blank cells in Power Query:

  1. Demote the header row by using the “Use Headers as First Row” option in the Use First Row as Headers split button drop-down on the Transform tab.  The header row will become the first row in the table.
  2. Press the Transpose button on the Transform tab.  The table is flipped on its side and the rows become columns.
  3. Select all columns except for the first column.  Select the 2nd column, then hold Shift and select the last column.  Or Ctrl+A, then Ctrl+Click the first column.
  4. Press the Fill > Down button on the Transform tab to fill the null (blank) cells.
  5. Press the Transpose button on the Transform tab to transpose the table again.
  6. Press the Use First Row as Headers button on the transform tab to bring the headers back.

This technique works well if you want to fill across the entire table.  If there are additional columns out to the right that could contain blanks, and you don't want those to be filled, then it might be better to use an If statement (conditional column) to create the filled values in new columns.

Thanks to A Rakesh for posing this question on the other article to fill down blanks with Power Query.

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >