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.

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.

When Can I Use This?

Here's an example of when you could use this technique.

The data set is the results of how many questions a student was able to complete on a test.  The result is a Yes if they completed the question and the first question they didn't complete is marked No.  The rest of the cells in the row are blank, and we need to fill those cells with No values.

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