How to Save Time in Finance with ChatGPT and Power Query: An Example

If you are part of an accounting close, you are probably no stranger to non-value-added manual processes. A classic one would be downloading some files off of our accounting software, copying and pasting into a working paper before transforming the data. These steps are critical but they are repetitive. They often don’t require any critical thinking other than trying to recall what we did last month. Yet, the time it takes to do those tasks add up. Thirty minutes per task multiply by ten tasks quickly become five hours you will not get back.

Since I have started using ChatGPT daily at work, I have been treating it like an all-in-one search engine and competent coworker. Recently, I decided to set aside some time to streamline our GST/HST working paper by applying what I learned about Power Query. However, since I am a Power Query beginner, I thought ChatGPT would be a handy tool to help me achieve what I want without the necessary knowledge. Spoiler alert, it did.

In this post, I want to share a step-by-step example of how I saved time with ChatGPT and Power Query in Finance. Specifically, how I streamlined our GST/HST working paper.

The Old Process

  1. Download three files from our accounting software:
    • vendors list
    • GST/HST GL data dump for the month
    • billings summary for the month
  2. Copy and paste each files from Step #1 to three different sheets
  3. Format data, filter and add/remove columns (some with formula)
  4. Review transactions for accuracy and compliance with tax laws

Step #2 and #3 are non-value-added steps that are time-consuming and repetitive. Also, these steps are prone to human error. My objective with Power Query is to automate these two steps so I can get more done with less time while maintaining or even improving accuracy.

The Power Query Process with ChatGPT

  1. Download three files from our accounting software:
    • vendors list
    • GST/HST GL data dump for the month
    • billings summary for the month
  2. Go to Data > Refresh All
  3. Review transactions for accuracy and compliance with tax laws

While we only cut one step down, new Step #2 takes less than 5 seconds to do. Unlike the old Step #2 & #3 which used to take me at least 20 minutes (longer if it’s your first time).

The Step-By-Step Process of How I Saved Time with ChatGPT & Power Query

Wherever applicable, I put the name of the Power Query step at the front of the step. You will also see that I ask ChatGPT to be concise as I wanted the steps without explanation.

1. I staged the working paper (WP) with the following sheets: Summary Page, Vendor List, GST/HST GL and Billings Summary

2. I downloaded the three files off of my accounting software and saved them in the same folder as my WP

3. [Data Import] I needed to import the CSV files into my WP so I asked ChatGPT for instructions:

how to import csv file on power query for transformation? be consice

4. [Merge Queries] I then wanted to add the vendor category from Vendor List to the GST/HST GL by matching the vendor names in both queries. Here is how I phrased my question for ChatGPT:

how do I link the vendor name on both data together so I can add the Vendor Category on Vendor List to GST/HST GL?

5. [Enter Data] On the Billings Summary data, I wanted to add some calculation columns. To do so, I need to first add a database containing the tax rates. I did not want to add that table to the WP so I asked ChatGPT how to enter a lookup table directly on Power Query:

how to store a lookup table on Power Query? I don't want to store it on my excel file.

6. [Merge Queries] I followed the instructions from Step #4 and added the tax rates from TaxGrpRates to the Billings Summary by matching the Tax Group in both queries.

7. [Custom Column] I then wanted to add a new column on Billings Summary to calculate the tax amount by multiplying the net amount of the bill by the tax rate. I asked ChatGPT:

On Power Query, I have two column called 'bill net amount' and 'tax rate.' How do I create a new column called 'tax amount' where it multiplies each other together?

8. [Close & Load to] Now that I am satisfied with my tranformed data, I was ready to import them into my WP. I asked ChatGPT:

I am done with data transformation. How do I import my transformed data into a specific worksheet? For example, my GST/HST GL query should go into the GST/HST sheet.

9. Now, whenever I download new source files from my accounting software, all I have to do is go to Data > Refresh All.