INTHEBLACK September 2021 - Magazine - Page 72
WORK SMART
// E XC E L YO U R S E L F
STORY NEALE BLACKWOOD CPA
IMPORTING EXCEL
FILES USING
POWER QUERY
IMPORTING MULTIPLE EXCEL FILES IS A STRAIGHTFORWARD
PROCESS THAT CAN BE AUTOMATED, PROVIDED THE FILE
STRUCTURES AND NAMING CONVENTIONS ARE CONSISTENT.
I
have three Excel state report files in a folder.
There are other files in the folder as well.
I want to import all three report files and
convert the report layout into a data layout.
I can then use the data layout with pivot
tables to report on the combined data.
The top section of the report layout can be
seen in Figure 01. The goal is to create a data
layout as shown in Figure 02.
This is something that used to be achieved
with custom written macros. With Power
Query, this process can be automated
via the user interface with one
straightforward formula.
This is a scalable technique. If we add
another state report file to the folder,
01
72 ITB September 2021
it will automatically be imported the next time
the data is refreshed.
The other files in the folder will need to be
ignored. A naming convention allows you to
filter out unnecessary files. If other files have
similar names, you may need to rename them
or remove them from the folder.
VERSIONS
I will be using the subscription version of
Excel for this example. Older versions may
have slightly different button labels. Power
Query is constantly being updated, so
changes are inevitable. Power Query is on
the left-hand side of the Data ribbon. It has
its own ribbon in earlier Excel versions.
PROCEDURES
1. In the Data ribbon, click the
Get Data drop-down and
choose From File, then choose
From Folder.
2 Navigate to the XL_Data
folder. Select the folder
and click Open. All files,
in any subfolders, will also
be imported.
3. An interim window opens
showing all files in the
folders and subfolders. Click
the Combine drop-down
and choose Combine and
Transform Data.
4. Another interim window
opens. This lists the sheets in
the file. It defaults to using the
first file to base this list on.
Click the Sample File dropdown and select one of the
Neale Blackwood CPA runs A4 Accounting,
providing Excel training, webinars and consulting
services. Questions can be sent to a4@iinet.net.au
files with a name that starts with Report_.
Click the Report sheet underneath, under
Parameters. The sheet structure will appear
on the right of dialog. Click OK.
5. The Power Query Editor window opens. In
the table that displays, use the filter icon on
the first column to select Text Filter, then
Begins With. Type in Report_ and click OK.
This will exclude unwanted files. I have used
the underscore character in the file naming
convention. Use a Text filter and Ends With,
and enter .xlsx. This ensures only Excel files
will be included.
6. The Power Query steps so far are listed on the
right of the screen. These steps are repeated
each time the data is refreshed.
Note: When working in the Power Query
window, there is no Undo or Redo icon. You
can undo steps by deleting them using the
red cross on the left of the step, but there is
no dedicated Undo option.
7. Right-click the first column header and
choose Replace Values. In the top box,
enter .xlsx and leave the bottom box
empty. Click OK. This removes the Excel
file extension from the file name.
8. Right-click the first column header and
choose Split Column, then choose By
Delimiter. In the top drop-down, make sure
--Custom-- is selected and the underscore
character is shown below it. Click OK. This
splits the Source.Name column across three
columns – see Figure 03. A delimiter is a
character (or characters) that separates
parts of a text entry or code. In our case, the
underscore character separates parts of the
file name that we can use as column entries.
9. Right-click the first column and remove it.
10. Double the column headers to rename the
first five columns in the following sequence
across the table: Date, State, Detail, Actuals
and Budget. The columns on the right that
weren’t renamed can be deleted.
11. On the Home ribbon, click the Remove Row
drop-down and choose Remove Top Rows.
Enter 5 in the dialog that opens and click OK.
12. Use the filter icon on the Detail column and
untick the null option at the top of the list.
In Power Query (null means a blank, or no
entry). Note: Whenever you refer to null,
it is always entered in lowercase. Power
Query is case sensitive, so be careful when