Power Query is meant to be an ETL tool, but I often find myself using it in addition to Excel. Power Query has some neat functionalities that make transforming data a lovely, summer breeze – like experience. We’ll look at three such functionalities: fill, split and lookup. In this article we’ll cover the fill feature in Power Query.

How-to

We use fill down very often. It works well in simple cases, but let’s say I have two values I want filled down, like in the example below.

Select the area you want filled.

Then select Editing > Fill > Fill Down.

The result below is not what we wanted. We’ve got the first value Legends filled all the way, overriding the Stars value.

What we have to do here is first select the area for Legends and fill down.

We then repeat for the Stars value. But this gets very redundant if we have a large dataset.

Let’s do this in Power Query. We load the table to Power Query.

Select the Category column and then Transform > Fill > Fill Down.

We get the following result.

Do you see how it did it correctly? Brilliant!

This sums up the demonstration of superior fill feature in Power Query. Stay tuned for the following parts, where we’ll show how to split data into columns or rows and join tables with ease!

Watch the tutorial

Meanwhile, you can also watch the tutorial on our YouTube channel!

Please leave us a like, comment, and subscribe for more amazing Excel tricks!

Follow us on LinkedIn.

Check out R Academy!