Using Power Query to Fill Down is an incredibly useful tool when working with starting files that are highly formatted reports that don’t output a date or key value on every row of a dataset. We’ll explain how to use Power Query to fill down to prep a dataset in Power BI or for use in Excel Power Pivot.
The following screenshot is an example of a starting report that might look similar to something that you’re working with as input data. Note that the date column has empty rows that need to be filled down.
Let’s take a look at how to get this done in Power Query!
Table of Contents
What is Power Query?
Power Query is a user friendly data transformation tool published by Microsoft across a number of different products including, Excel, Power BI, and Dataflows. It’s been around since Excel 2010 and has been consistently updated over time adding new features and functionality.
It’s incredibly useful for recording repetitive data prep steps and automating data processes. For people interested in automating and streamlining their data processes it’s a great starting point. Skills learned in Excel Power Query are directly transferable to Power BI.
Similarities Between Power Query for Power BI and Excel
One of the biggest advantages of Power Query is its consistent user experience across different platforms, primarily Excel and Power BI. Both these applications offer a very similar set of functionalities when it comes to Power Query. The user interface, the query options, and even the M language (a functional language used in Power Query) functionalities are the same across platforms.
The largest difference between Power Query in Excel and Power BI is what you do with the data once it’s transformed and prepped. Excel is awesome at creating highly flexible reports in a traditional grid format while Power BI is designed for drillable and explorable data visualizations presented on dashboards full of charts, graphs, and KPI’s.
In relation to filling down, Power Query is much faster than writing a custom formula in Excel. In Power BI it’s necessary to use Power Query to prep data before being able to create visualizations.
When to Fill Down using Power Query
Filling down using Power Query is a quick and easy way to ensure that all of your rows have data. It’s an essential step to having data that’s in a tabular format. Meaning that it’s a fully filled out grid of rows and columns. This format is a key part of working with data at scale and makes an Excel or CSV file look more like a database.
When working with data, one of your first objectives should be getting all of your data into a table for a number of reasons.
- Time Series Data – If rows are missing specific dates they will not be presented on a chart or graph appropriately.
- Continuous Data for Grouping – Values that are not filled down will be presented as blank and be left out of pivot tables or other visualizations when applying a filter or calculations.
- Data Normalization – Having all rows filled down makes for a continuous table that can easily be joined and blended with other datasets.
Now let’s look at how to fill down using Power Query!
How to Fill Down Using Power Query
The first step to filling down in Power Query is launching the Power Query editor using the platform of your choice. Then right click on a column header that has the empty values that need to be filled and select fill down or fill up. Power Query is capable of filling missing rows of information in either direction.
Let’s break it down into more detail and then look at some common issues that come up when trying to fill down and how to handle them.
The fastest way to fill down is to right click on a column header and navigate to Fill, Down or Up.
Alternatively, you can use the fill down button from the Power Query Ribbon.
It’s important to keep in mind that this method will only work if the values in the column being filled are null instead of blank or another value.
Now let’s look at common issues that can occur when trying to fill down. You might notice these problems if you click on Fill Down the applied step is added but then nothing happens.
Common Problems and Troubleshooting
There are a couple of problems that come up when filling down that we want to make sure that you’re aware of if you start to use the function and it doesn’t seem to be working.
Power Query Fill Down Not Working
The first thing to check is to ensure that all of your values are null below each section that needs to be filled in. Even if a cell looks empty or blank the Fill Down function will not work. The example below shows blank values and a fill down that did not fill down the values correctly.
To fix this, right click on the column name and replace values. Replace the blank values with null then use the applied steps. (To replace null values you will leave the from box empty, and then type the word “null” in the replace with box)
After your data is setup you will be able to fill down as necessary.
Values Remain Unfilled
The order in which values are placed in the dataset will determine if there is anything to fill down. Rows at the top of the dataset that don’t have any values above them will remain unfilled. It’s also possible that there are extra values somewhere in your dataset that are not null, such as white space that will cause a fill down to look like it’s failed.
In these scenarios, you may need to adjust the actual dataset, or try to turn on Column Profiling to get an idea of what the values are in a specific dataset if it’s too large to manually review.
Column Profiling can be turned on under the View, Column Profile Options.
After fixing any existing data issues or finding any values that don’t below and replacing them with nulls the fill down function should work as expected. The column profile should also indicate whether it was successful or not.
Conclusion
The Fill Down function in Power Query is a simple yet powerful tool for business users working with Excel or Power BI. At it’s core, you can simply right click on a column name or hold down CTRL or SHIFT to select multiple columns at the same time and fill them. The key to remember is that fill down only works when data is sorted in the appropriate order and will only replace null values.
Don’t forget to prep your data head of time, by pre-ceding Power Query steps and it should be a quick easy solution!