13 December
CPA PD
By Jennifer Chun

An Example of How Power Query Automates Review & Saves Time (+ ChatGPT Prompts)

I want to share an example of how a Treasury Manager spent 20 minutes to save time and enhance accuracy with Power Query via automation. The set up took about 20 minutes (longer as I walked the person through the steps slowly). The automation saves the user 9 minutes each time the review is done and the task is currently done twice a week. This equals to saving over an hour a month with just a 20-minute investment.

The best part? You don’t need to be a Power Query expert to accomplish this as ChatGPT can walk you through the steps as well. To help, I have included sample ChatGPT prompts you can use in this post.

Want to learn more ways to leverage AI in Finance? Read more posts like this here.

Contents show

Sample ChatGPT Prompts for Power Query & Conditional Formatting

  1. On Power Query, how do I import an Excel workbook? I want to create a pivot table after. Please list the steps in a concise way but keep in mind I am new to Power Query. I have Excel for Microsoft 365.
  2. On Power Query, how do I import a PDF? I also want to format the data before loading it to a pivot table.
  3. On Excel, how do I create conditional formatting for when a cell is not empty and does not equal to 0?

Current Payment Verification Process (~10 minutes)

Purpose: To compare the list of outstanding vendor bills against the wire payments set up by the Junior Accountant to ensure the data is correct (recipient, bank account and amount); this is a crucial review step as it has direct financial impact

1. Treasury Manager receives an Excel file that came from their accounting software containing a list of outstanding vendor bills to be paid (fictional data created by ChatGPT):

2. Treasury Manager also receives a PDF file from the bank containing a list of wire payments that were set up by their Junior Accountant based on the list from Step #1:

3. Treasury Manager manually creates a Pivot Table with the table from Step #1 resulting in a similar format as the PDF (note there is more than one invoice for some vendors such as Preston):

4. Treasury Manager arranges the PDF and Pivot Table side-by-side and review manually for accuracy.

Revised Payment Verification Process (1 Minute)

Purpose: same as above

  1. User saves the two files from Junior Accountant in the folder where the Review Working Paper is
  2. User opens the Review Working Paper, goes to Data > Refresh All
  3. Excel automatically does the comparison and highlights any discrepancy in red (recipient name, bank account, amount)

Power Query Step-by-Step Set Up Process

  1. Creates a new Excel workbook and names it Review Working Paper
  2. Imports both files from the Junior Accountant via Power Query:
    • Excel file: Data > Get Data > From File > From Excel Workbook; select Table object > Transform Data > Close & Load To > Pivot Table Report
      • resulting in a Pivot Table that looks like the one from the old process
    • PDF: Data > Get Data > From File > From PDF; select Table object > Transform Data > Close & Load To > Pivot Table Report
      • resulting in a Pivot Table that has the same format as the previous one
  3. After loading the two pivot tables side-by-side, create a CHECK section as seen below with formula and conditional formatting:
    • NOTE: I changed the last digit for Preston on the Excel File (left table) to demonstrate the review file’s ability to highlight minor variance that is hard to spot with human eyes