The CPA Revolution: Process GL data

New to this blog? Start the Intro to Power Query series here.

As an accountant, I've spent countless hours taking general ledger (GL) data and modifying it in Excel to get it to look like how I need it: removing columns, reformatting data types, unmerging headers.
Sound familiar?

In this exercise, we are going to start to learn the Power Query Editor, a user friendly automation tool. In the PQ Editor, set-up rules, then next time just Refresh and the data is just how you need it. Bye-bye manual work.

Getting Started

Recall that we are trying to produce the following report using the GL data below.

Normally, you would clean-up the data manually. Now we are going to use the PQ Editor to transform the data in to these columns.

"GL Account" with a 4-digit GL account number, no commas,
"Dept" with a 3-digit department number with leading zeroes,
"Dept Name"; and,
"Balance" with a dollar sign and two decimal places.

Open the file you created from the prior post, or start with this file.

Show the queries by selecting on the Data tab, Queries & Connections (Show Queries in some Excel versions). Right click on the "GL_bal_Data" query and select "Edit" to open the Power Query Editor.

Split Column by Delimiter

First, let's create a column which contains just the 4-digit GL account number.

Select the Account column by clicking on the column header "Account". On the Home tab, select "Split Column", then "By Delimiter".

Change the "Select or enter delimiter" to "--Custom--" and a dash "-". Select to Split at the "Left-most delimiter". "Ok".

PQ has now split the account column, guessed that the data type of the new column is a Whole Number (123) and recorded the steps under Applied Steps.

Hope that was simple enough.

Let's do it again. This time split the "Dept" column.

Did you happen to notice how many additional options PQ provides for splitting a column compared to native Excel? Splitting by Custom I've been able to use 250 characters to split a column compared to native Excel's one character limit. Next time you find yourself writing a LEFT, MID or RIGHT formula, consider loading the data in to PQ instead.

Applied Steps

Did you notice the Applied Steps area on the right side of the PQ Editor?

In plain English, the Applied Steps area shows each step which has been taken on the query. This makes it really easy to make a change to the query in the future, if needed.

To make a change, select the gear button next to the step. For example, the Split Column by Delimiter menu would pop up allowing you to make any necessary changes, if needed. PQ shows the gear on editable steps; no gear is shown on steps that PQ autocreates, such as determining what type of data is in a column.

When selecting a step, the preview shows how the data looks after that step has been performed. For example, if you select the step Split Column by Delimiter the Account.1 column is of a type ABC (text). If you then select the Changed Type1 step, Account.1 will be a type 123 (whole number).

Plain English steps and step-by-step previews is where PQ far exceeds macros in regards to durability, ease of use, and long term adoption.

Remove and rename

We have the 4 columns we need, plus one extra. Remove the extra column by selecting "Account.2" and then selecting "Remove Columns" from the menu above.

Next, rename the columns "GL Account", "Dept", "Dept Name" and "Balance" by double clicking on the current column headers.

Types

Looking at the column headers, notice they are of types 123 (whole number), ABC (text), and 1.2 (decimal number). When working with data in PQ, PQ will attempt to determine the data type which best supports efficient storage, calculations, and data visualization. However, PQ doesn’t understand how you are going to use the data, and therefore, doesn't always get the data type as you need it. Certain functions and processes only work with specified data types, and so having the correct data type is an important concept.

💥BEST PRACTICE TIP
If a column contains numbers you would never sum, then change the type to "Text". In our example, we would never sum the GL account numbers or department numbers; therefore, they should be "Text" not numbers. 💥

Let's change the type for the GL Account column "Text". On the column header, click on the "123". Select "ABC Text".

Now, select the column "Balance". On the Home tab, select "Data Type: Decimal Number" and change it to "Currency".

You've now learned two different ways to change the data type. In practice, the latter approach is best when changing multiple columns to the same data type, while the former approach works best for individual column changes.

After PQ attempts to determine the column Type, it auto-creates a Changed Type step. If the Dept column is of type Number, leading zeros will be removed. Since we want to retain the leading zero, we need to change the Type to Text. Highlight the Changed Type step, then on the Dept column header, click on the "123". Select "ABC Text".

When prompted, "Replace current" step.

TRIM

Just like in native Excel, TRIM in PQ removes extra spaces at the beginning and end of a cell. First, select each of the three Text columns holding down Ctrl. Then, on the Transform menu, select "Format" then "Trim".

"Close & Load".

In this lesson, you learned how to:

Select from the Power Query Editor menu to record automated steps,

Use the gear buttons to change steps,

Split columns,

Remove columns,

Rename columns,

Modify the column type and,
TRIM excess spaces.

SOLUTION

Prior post: Refresh
Next post: PowerBI and PowerPivot

💬 How did the lesson go? Any roadblocks?

Process GL data