Are you using Power Query to import data into Excel and finding that one of your columns is missing data. It is not really totally missing, but you wish that you could fill those empty rows with the data that is found in the row above.

Check out the YouTube video called Easily Import Data from Web to Excel (2 Practical Examples) by Leila G. at time 3:02.

You might need to ensure that your blank cells in the Power Query table are actually blank. If they are merely empty, such as an empty string, we will want to replace empty strings with null. They may also have hidden characters, which need to be replaced with null.

Below is a simple Excel range of cells that we are bringing into Power Query. First put the cursor inside the range, Data tab, From Table/Range and be sure that you select My table has headers.

The Power Query Editor.

It didn’t work? Perhaps those empty cells in the Product column are not really empty. If we replace those cells with null, they will truly be empty and the Fill Down should work. Use the Transform tab, Replace Values, and just don’t enter anything in the Value to find box.

Now you can see the word null in those empty Product columns. Now go to the Transform tab, Fill, Down.

Notice that I changed the name to ProductsQuery on the right side. When I Close and Load to Excel, it will create a new sheet with the name ProductsQuery. It will also put the data into a table.

Compare R Language

Do you know R language? Do you want to know how to do this very thing in R? Check out the video called R programming for beginners: Select, filter and fill functions within the tidyverse. It starts at time 5:37. This is part of a data analyst’s cleaning.