The Fill Function

In this lesson we‘re going to look at the Fill function of PowerQuery. This is a great function which allows us to fill cells in a column which have null values (where PowerQuery identifies that there is no value in a cell), with the last preceding or succeeding cell which has a value in it.

Here’s an example: Let’s say we have a column with 5 values. In the first cell we have the word, ‘Power’. The next three cells have null values, while the final cell has the value ‘Automate’. We can use the Fill function to fill all cells with the null values either with ‘Power’ if we fill downwards, or with ‘Automate’ if we fill upwards.

So how do we do this?

The Fill function

Once you’re in the PowerQuery editor:

1. Highlight the column with the nulls that you’d like to fill
2. Click on the Transform tab
3. On the ‘Any Column’ section of the ribbon, select the ‘Fill’ drop down.
4. From here you can select to fill up or fill down. Fill Down takes the last value in a cell before the null cells and that uses that value to fill in the nulls. Fill Up takes the first cell with a value after the nulls and then fills the cells upwards.

Simple!

Appending reports

We also have another look in this lesson at appending reports together (just a reminder: this is like copying and pasting a report one under another).

We’ve covered this topic previously, but it’s worth having a more in-depth look at how the append function works.

The append function looks at two or more queries and puts them one under the other, to create a long query consisting of the two or more reports. Columns that have the same header in each of the queries, are put underneath each other forming a single column. However, where the headers differ then additional columns will be created in the appended query.

Let’s have a look at an example:

Let’s say we have two queries that we want to append together, each of which only has one column. In both queries, the column is called ‘Supplier’.

In this example, an appended query consisting of these two queries, would only have one column called ‘Supplier’ as the sole column in each of the constituent queries is called exactly the same name: ‘Supplier’.

In another example where again we have two queries that we want to append together with only a single column, but this time, the column in one query is called ‘Supplier’ while in another it is called ‘Vendor’. An appended query from these two queries would result in two columns; one called ‘Supplier’ and one called ‘Vendor’. Where there were values in the Supplier column, there would be nulls in the Vendor column and vice versa.

One more example, let’s say we have two queries for appending, each of which only has one column. In one query, the column is called ‘Supplier’ and in the other it is called ‘supplier’.

In this example, an appended query consisting of these two queries would again have two columns. This is because PowerQuery is case and character sensitive in its code and therefore ‘Supplier’ and ‘supplier’ are different headers.