How to create a Journal Entry with Power Query

Create a journal entry using Power Query. In this post you learn how to create records, sum columns and combine datasets using Power Query.

A client once asked me if we could create a journal entry in Power Query. Each month they needed to upload bonus-reservations in their accounting system. And the software allowed them to upload a CSV file, as long as it was in the required format.

Since this was such a repetitive process, it was the perfect candidate for automation. After all, the only parameters it had, were the bonus amount-bookings on the one hand and the counter-booking of the reservation on the other hand.

1. Requirements

In accounting, a journal entry needs to have the same debit as credit amount. For the purpose of this solution, it has to meet the following requirements:

  1. The bonus reservations need to be specified on employee level on account 1620
  2. The total reservation of the month is booked on General Ledger 1420.
  3. The journal entry needs a booking Period

2. The dataset

The dataset originated from an Excel worksheet. The bonus amount an employee receives was calculated and imported into Power Query. The result of that is the below picture:

From the starting position you will now learn how to apply the relevant transformations.

3. Adding the General Ledger

The visible lines amount to the total Bonus reservation and are booked on the Debit side of the journal entry. You can add a custom column, name it General Ledger, and enter the account number in the formula box = 1620.

This will result in the following formula:

= Table.AddColumn(#"Changed Type", "General Ledger", each 1620 )

And your dataset changes to:

Add account number for journal entry

We don’t need to add any parameters here, because the bonus reservation is always booked on this account.

4. Calculate Total Bonus Amount

A journal entry has the same amount debit as it has credit. Our next step is to calculate the credit amount of the journal entry. You will add a new record to the dataset, with the credit booking of the total bonus amount. But how do you calculate the total sum of the Amount column?

The easiest way is to select the Amount column. Then navigate to the Transform tab, click on Statistics and select the Sum operation. A new step will be created with the below code:

= List.Sum( AddGeneralLedger[Amount] )

We can use this code when generating a new record in the dataset.

Before moving on, what does the above code do exactly? In Power Query referencing a column results in a list containing all the values from the column. You can do that by writing the Previous Stepname and putting the Column Name between square brackets:

= AddGeneralLedger[Amount]

If you add a new step (by clicking the Fx button) and paste above code, the result will be a list like below:

Since the previous result is a list, you can wrap it around the List.Sum function, to Sum up all the values in the list. We now know the total bonus amount. What comes next is to create a new record from scratch.

5. Create a Record

You will now learn how to create a record, with a column for each of the existing columns in the table. In this example you have the columns: Name, Amount and General Ledger. But first, how do you create a record?

Creating a record in Power Query is easy once you know it. You can add a record with a single column by pasting below code in the formula bar:

= [ Column1 = "A" ]

When you require multiple columns you can expand the formula with below syntax.

= [ Column1 = "A", Column2 = "B", Column3 = "C" ]

The general syntax for creating a record is:

  • Start and end the formula with square brackets
  • Between the square brackets, provide the column names without any quotations, followed by an equal sign and a value.
  • Separate column names by a comma.
  • The column value can be anything. It could be a number, text, list, record, table, binary item etc. Anything that can be put into a regular table cell is valid as a column value.

For our exercise you can create the necessary record as follows:

=[ Name           = "Bonus Reservation", 
   Amount         = - List.Sum(AddGeneralLedger[Amount] ), 
   General Ledger = 1420  
 ]

The formula for the Amount value is the one we created in the previous section. The only difference is that it is presented negatively, to represent the credit amount. After renaming the step to CreditRecord, the query looks like this:

Create record from scratch

6. Combining the data

So far, we have prepared our data for the debit and credit side of the journal entry. We will now combine the data. There are several functions that can do that.

6.1 Method 1: Combine using Table.InsertRows

Power Query has a function called Table.InsertRows. This function takes an existing table, and adds rows to it. The three arguments for the function are:

  1. The table to add rows to
  2. The (index) position where the new rows will be inserted
  3. The new rows to add to the dataset, input as a list

With this function we can easily combine our previous steps. Start by writing the following code:

= Table.InsertRows(AddGeneralLedger, 0, CreditRecord)

AddGeneralLedger is the step that contains all the debit journal entries, and CreditRecord contains the rows we want to insert. You will notice that Power Query throws an error saying: “Expression.Error: We cannot convert a value of type Record to type List.

When you look closely at the third argument, it indicates that the new rows added, need to be provided in list form. With a simple adjustment you can fix the code. Wrapping the CreditRecord between curly brackets turns it into a list.

= Table.InsertRows(AddGeneralLedger, 0, { CreditRecord } )

And with that, the data combines as in below picture:

6.2 Method 2: Combine using Table.Combine

Allow me to show you another way of combining the data. The default function Power Query uses to combine tables is Table.Combine. This function is used when you apply the Append Queries operation. Table.Combine takes a list of tables as its main argument.

A wrong solution would be to write:

= Table.Combine( { AddGeneralLedger, CreditRecord } )

The error you get is: “Expression.Error: We cannot convert a value of type Record to type Table.” The reason is that CreditRecord is provided as ‘Record’ object, and the function Table.Combine expects a table. What you could do instead is:

= Table.Combine( 
     { AddGeneralLedger, 
       Table.FromRecords(  { CreditRecord } ) 
      } 
  )

What’s different from the wrong solution is:

  • The CreditRecord is transformed to a table using the Table.FromRecords function.
  • Both tables are wrapped between curly brackets and separated by a comma.

As you can tell, the data types provided to function are very important in Power Query. Getting comfortable manipulating your data types, allows you to use different ways to get to the same end result. Either one of the two methods will combine your data into a single dataset. Now there is only one thing left to do, adding a book period.

7. Add the booking period

Our last requirement is to add a booking period to the journal entry. This value needs to change for each journal entry. To make things easy for your user, you can create a parameter for the booking period. To do that:

  • Right-click in the query pane, select new parameter.
  • Name your parameter BookingPeriod. Tip: not having any spaces in the name makes it easy to reference the parameter in your code.
  • Change the type to Date and enter a current value. In this example I used 31/01/2021.

With the parameter in place, you can now add a custom column. Name this new column Booking Period, and add the formula:

= BookingPeriod

Change the new column Booking Period to a date type. And with all those steps combined, you know how to create a journal entry in Power Query

If you want to have a look at the file itself, you can find it here:

Enjoy !

About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

2 thoughts on “How to create a Journal Entry with Power Query”

  1. Thank you!. What an interesting post. In fact, I’m working on a Journal Entry project and got questions you might be able to help with. I have created a solution that after a bunch of data pull and transform steps created both the debit and credit sides of the JE, Union them to a single dataset which is fine. This is a repetitive task and I want to automate it fully. So, to this JE dataset, I’d like to add a column (identifier to keep track of data flow) that reads a parameter (hands-off), adds one to it, and fill out the new column with these sequence IDs. Any help with this is much appreciated.

    Reply

Leave a comment