Power BI MCSA Certification Tips – Part 5: Fill Down
To obtain your MCSA: BI Reporting you need to pass two exams: 70-778 (Analyzing and Visualizing Data with Power BI) and 70-779 (Analyzing and Visualizing Data with Excel). However, there is a huge overlap between the two exams resulting from the common BI features found in both Power BI and Excel: namely data modelling using the DAX language; and data connection and transformation using the M language.
We, at G Com Solutions, recommend to the clients to whom we supply Power BI Training that they prepare for both exams simultaneously and then sit both exams on the same day. We also run a 5 day Power BI MCSA Certification Prep course in London which includes both exams 70-778 and 70-779.
In this series of blog posts, we will be examining some of the key features of Power BI, Power Pivot and Power Query which you will need to master in order to pass the two exams.
In this topic, which applies equally to both Power BI and Excel Power Query, we will examine the use of the Fill Down command.
The Fill Down / Fill Up command is designed to correct a problem encountered when connecting to data which is, in reality, a report generated by another system. It is not uncommon for this type of data to use subheadings within a column, which means that this column contains many rows which are blank, but which, for analysis purposes, should really repeat the appropriate subhead on every row. Fill Down is more commonly required than Fill Up; but once you have used Fill Down, you can see exactly how Fill Up would work.
Syntax
Table.FillDown(
table as table,
columns as list
) as table
· Table: The table to be transformed
· Columns: A list of the columns in which the fill down operation will take place
An example of data containing a summary column with blank rows is shown below.
If we are going to analyze the data, we must have a value on every row of the Category column. Instead, we simply have, for example, “Accommodation” acting as a heading, rather than as a data entry.
The Fill Down command will take each of the headings and copy it down into the blank rows below; but it will never overwrite an entry, since only empty rows will ever be filled.
If we right-click the Category column and choose Fill > Down, Power BI produces a line of code similar to the following.
#”Filled Down” = Table.FillDown(
#”Replaced Value”,
{“Category”}
)
(#”Replaced Value” is the name of a variable containing a table reference.) Note how, even though only one column is being affected by the operation, the second parameter is still a list.
Fill Down Requires Nulls
The Fill Down command will only replace null values, and Power BI does not treat a blank entry as null. Thus, in the example shown above, although the Filled Down step is created; nothing happens to the data; the blanks are still there.
This is because the cells that follow each entry are not null cells; they are blank. Whenever this is the case, before the Fill Down command can be used, we must first replace these blanks with nulls.
A very useful feature of the Query Editor is the ability to insert steps to fix an issue that you have encountered. There is no need to remove the problem step; simply highlight a previous step and insert a step which supplies the fix. Thus, in the example above, we can insert a Replace Values step (before the “Filled Down” step) to insert nulls instead of blanks; entering nothing in Value to find, and “null” in Replace with.
Now that we have null values instead of blanks, when we highlight the final step, Fill Down will produce the desired result.