Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

There are situations where you may manually want to add rows to a table. For example, when creating the bookings for journal entries using Power Query. In this post, you learn how to create records, sum columns, and add rows to tables using Power Query.

Table of contents

Introduction

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.

How to create a journal entry with power query

Requirements

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

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

The dataset

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

1. Journal Entry The dataset

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

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.

Calculate Total Bonus Amount

A journal entry has the same amount of 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 the above code, the result will be a list like below:

3. Journal Entry Referencing a column results in a list

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.

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 the below code in the formula bar:

= [ Column1 = "A" ]

When you require multiple columns, you can expand the formula with the 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  
 ]

In the previous section, we created a formula for the Amount value. The only change now is that it is displayed negatively to represent the credit amount. We renamed the step to CreditRecord, and the query now appears as follows:

Create record from scratch

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.

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, are 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 the below picture:

5. Journal Entry Combining the data

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.
6. Journal Entry Combining the data method 2

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: add a book period.

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 and 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
7. Journal Entry Add Booking period

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

Conclusion

And that’s how you can create journal entries using Power Query. If you want to have a look at the file itself, you can find it here:

Enjoy Power Query!

Share this post:
  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
    • Hey Nima,

      That sounds awesome. Can you perhaps send a sample file and describe your desired outcome. I don’t fully understand your requirements yet

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.