5 tips: Power Query for Accountants (and finance people) – Free Masterclass

Do you work with accounting or finance data? You are going to love, no scratch that, adore Power Query. It can save you precious time, make you look like a hero in-front of clients and keep you sane.

In this masterclass, learn all about Power Query for accountants (and other kinds of finance people) & 5 tips.

This is part of Power Mondays series, where every Monday we discuss Power BI / Power Query / Power Pivot.

Why Power Query?

Power Query is a data processing software. Using Power Query, you can create process driven programs (queries) to collect data, clean or reshape it, calculate things and publish final output as a table. Once you create a query in PQ, when the underlying data changes, you just refresh the query and brand new data will be fetched, cleaned and published for you.

For example, you can use Power Query to combine ledger files from various accounts to one master file with Power Query. No more VBA or manual copy pasting. Just one query and you are done.

In this lesson

This masterclass is broken in to 5 topics.

What is Power Query – Quick intro

We start by discussing what Power Query is, how to access it, understanding Power Query UI, how to enable formula bar and what various ribbons are.

Extracting data from bank statement

You will learn how to

1. Connect to a file on computer
2. Load bank transactions to a new Power Query connection
3. Delete transactions of certain type
4. How to categorize transactions by amount – small, medium or large txns
5. How to duplicate a query
6. Splitting transactions in to debits and credits

Combining monthly ledger data files to single table

In this section, learn how to

1. Connect to a folder full of similar format files
2. Set up sample query to teach Power Query how you want to merge files
3. Combine the data and publish
4. Create a pivot table report from the data
5. Update the folder and refresh
6. Look at modified pivot report

Reconciling Sent vs. Paid invoices

In this topic,

1. What is reconciliation
2. Loading tabular data in to Power Query
3. Connecting two tables using “Merge queries” option
4. Using left joins
5. Removing duplicates
6. Tagging invoices as either “Paid in full”, “Under paid”, “Over paid” or “Unpaid” using conditional column feature
7. Publishing results

Live exchange rate tracker

In this section, learn how to:

1. Connect to public websites to fetch currency data
2. Use Reserve Bank New Zealand’s currency rate data as connection
3. Reshaping data with “Transpose” and “Use headers as first row” options
4. Removing un-necessary columns
5. Publishing final exchange rate

Watch Power Query for Accountants – Masterclass

Please watch the lesson video here (or on Chandoo.org YouTube Channel)

Download Sample files for this lesson

Click here to download sample files for this lesson. The example file is crafted with love and care. Check out the queries by going to Power Query screens. Go thru the steps and enable formula bar to learn what is going on.

How to view / edit the queries?

1. Go to Data Ribbon
2. Click on “Show Queries”
3. Right click on any query and “Edit”

How to refresh the queries?

1. Go to Data Ribbon
2. Click on “Refresh All”
3. Done.

I get an error when refreshing…

1. The queries are mapped to local folder files
2. So you need to re-map them to folders on your computer
3. Edit the query with error (see steps above)
4. Go to Source step
5. Click on gear icon
6. Change file address as per your computer
7. Save & load the query

AND(You=”Accountant”, Use=”Power Query”)? Let’s talk

Are you an accountant (or finance pro) and use PQ often? Please share your tips, thoughts and ideas in comments section so I can learn from you. Getting on the Power Query bandwagon? Check out the intro to PQ and this lesson to get going. Post your wins in the comments.