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:
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.
Nothing new to add here…next lesson please!