INTHEBLACK September 2021 - Magazine - Page 73
CLICK HERE TO ACCESS
Extra material, a podcast
and a video from
Neale Blackwood CPA
02
03
04
typing entries. The null option is always
listed at the top of the filter list. By filtering
the table, you are reducing the number of
rows that will be loaded, which can speed
up refresh times.
13. We now need to work on the report
structure to capture the categories
as a separate column.
There are descriptive headings above the
value lines. We need to capture them in a
separate column.
14. In the Add Column ribbon tab, click the
Custom Column option. Change the name
to Category. In the formula box, enter the
following formula. Note that the = sign is
always in the Formula box.
if [Actuals] = null and [Budget] = null then
[Detail] else null
You can double-click the column names on
the right of the dialog to enter the names
surrounded by square brackets. This is
case sensitive, so be careful. If you have an
error, check both the spelling and the case.
15. Right-click the Category column and
choose Fill, then choose Down. This
captures the categories on the correct rows.
16. Filter the Actuals column and remove the
null entries.
17. There are headings in the Actuals column.
To remove them, click the ABC123 icon on
the left of the column header and choose
Whole Number. This generates errors for
all text entries.
18. Right-click the Actuals column and choose
Remove Errors.
19. There are total rows in the data. We could
leave them there, but it will be cleaner if
we remove them. Filter the Detail column
and use Text filters. Use two Does Not
Begin With criteria options, separated by
the And option. The first is Net and the
second is Total. Click OK – see Figure 04.
20. We are now ready to convert the report
into a data layout.
21. Move the Category column to the left of the
Detail column by dragging its header across.
22. Select the first four columns by holding
the Ctrl key down and clicking each
column. Then right-click one of the
selected column headings and choose
Unpivot other columns.
23. Change the Attribute column name to
Type. Change the Value column data type
to Whole Number.
24. Click the Home ribbon and the Close and
Load option on the left to import the data
from all three files.
The filter rules you use are important, and
need to be robust. They may require some trial
and error to perfect.
I used the null option a couple of times to
remove rows. If the report had used an empty
(null) cell for a zero value (for example, there
were no stock adjustments for the month), then
using null would not have worked correctly.
You should also check your Power Query
output against the original data to make sure
nothing has been missed.
intheblack.com September 2021 73