Power Query as an Audit Tool

Level: Beginners

Recently I was working for a client that required the matching of a list of data sent from a customer against some internal business data.  This type of data matching is actually an audit process that I would have historically done with MS Access, but it occurred to me that Power Query would be a very capable tool for the purpose.  Let me create a demo example to demonstrate the point.

Remittance Advice Scenario

Here is the demo scenario – you are a wholesale business and you sell to multiple customers.  Each customer has multiple invoices during a given month and the sales are all on credit.  At the end of the month you send the customer a statement requesting payment for a number of invoices.  The customer sends you a single payment for a batch of invoices along with a remittance advice (a list of what they are paying).  Your job is to check the invoices that have been paid against your accounts receivable system to make sure of the following:

1. All the invoices have been paid
2. Check for any invoices that have been paid that were not on this month’s statement (ie late payment from last month).
3. Check for any variances in payment.

For this demo, I have 2 source files

1. A remittance file from the customer that looks like this (truncated in this image).
   
   This is the list of invoices and amounts the customer actually paid.  The total of this remittance advice matches the total payment, but it doesn’t match the statement you sent.
2. An extract from your Accounts Receivable (AR) system that looks like this (also truncated in this image).
   

So basically the files are in the same (or at least similar) in format.  But your job is to match the customer’s remittance against your master AR records.

Here is how to do it in Power Query.

Create a new workbook and create a connection to both files

First connect to the remittance advice.

Select the sheet (shown #1 below), then select “Edit” (#2 below).

In this case the table is nice and clean so there is no need for any further transformation.  However you should change the name of the query (shown #1 below).  Then proceed to select “close and load” (#2) then “Close and Load to” (#3).

There is no need to load the data into the data model or to a Table at this time.  All that is required at this point is to create a connection.  So select the following options from the dialogue, and then click “Load”.

This creates a “connection only” to the file as shown

Now repeat the process for your AR Master data records.  Make sure you give this second query a logical name so you know what it is.  You will end up with something like this.

It is a good idea to group your queries so it is easier to work out what you have done.   Right click on one of the queries (Shown as #1) and select “move to group” #2, then “New Group” #3.

I called the new Group “Connections” as shown.

Once you have the new group set up, you can move the other query to this new group too.

OK, now it is time to write some new queries that refer to these connections and get Power Query to do the hard work of matching the data.  I will create 3 queries.

1. Invoices not paid
2. Invoices paid not on original statement
3. Invoices paid with payment variance

Query 1. Invoices not paid

From the Power Query menu, select “Merge”.

In this example, the AR listing is the “book of record”, so I will select that as the first table (shown as #1 below).  Then select “remittance” as the second table (#2).  Then you have to tell Power Query how to “join” the tables.  So click on the Invoice Number column in the top table (#3) followed by the same data column in the second table (#4).  I have kept the “Join Kind” to be “Left Outer Join” (#5) as I want to know which invoices are not paid.  Note that there are 2160 invoices paid out of a total of 2206 in the AR system (#6).  Finally click OK (#7).

Now select the “expand” button as shown (#1), deselect all columns other than “Payment Amount” (#2) , deselect the “original column name” (#3) and click OK (#4).

Anywhere there is a “null” in this new column, it means there was no payment to match the AR master list of invoices.  These invoices shown as “null” therefore have not been paid.

So filter out all the rows in the table except for the “nulls” as shown below.

You are finished with this column now, so right click on it (#1 shown below) and then remove it (#2)

Now give the query a name, “close and load” the query, but this time load it to a table in your workbook as shown below.

I then moved this new query to a new Group called “reports” (just like above).

Query 2. Invoices paid but not on original statement

To create this next report, I created a new query just like the one above, but I swapped the tables around so the remittance table is first.

Almost every other step is identical to the previous query, so follow the process above to get a new table that shows all the payments made that were not on the original statement.  However this time when I tried to apply the filter for “nulls”, I could not see “null” as an option in the list.  There was a message saying “List may be incomplete” (shown below), so I clicked “load more” (#1) so I could select just the null values as before.

After clicking “load more” I was able to complete the last steps as shown above in the first query.  Give the new query a suitable name and load it to a table only (as before).  Then move the query to the Reports Group.

Query 3. Invoices paid with payment variance

The last step is to check which invoices were paid with a different amount than was on the invoice (ie short paid, over paid).  This time I simply copied the first query “missed payments” and made some changes to the copy.  To do this, right click on the query (#1) and select “duplicate” (#2).

Give the new query a suitable new name (#1) and delete the last 2 steps by clicking the “cross” next to each of the last 2 steps (#2).

Now add a new custom column as shown below.  This new column is the amount expected to be paid minus the amount actually paid.

Now all you need to do is apply a filter to this new column and deselect the zero and null options from the list.  All the values that are left will be those that have a variance.

It is a good idea to give the column headings a new name so that the resulting table makes sense.

And that’s it – a reusable audit tool.  All you need to do to reuse the tools is re-point the first 2 queries to another set of files, and refresh the workbook.  It is possible to add an additional final step and append the 3 tables into 1 single table.  To do this you would need to add a new column with a descriptor like “Not Paid”, “Additional Payment”, “Payment variance” etc.

Here are the files if you want to download the samples and take a look.

If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/power-query-online-training/