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

Master Class , Power Query - 3 comments

Power query tips for accountantsDo 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.

 

 

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

3 Responses to “5 tips: Power Query for Accountants (and finance people) – Free Masterclass”

  1. JoAnn Paules says:

    I'd love to listen in but I start work at 6am so I'm not online at 8:30pm the night before. If I was, I wouldn't be able to spell Excel much less do any real work. Will this be recorded and available later?

  2. Sam says:

    I have a 7,000 employee, 18 site, multiple shift and production mix model now built and producing excellent quality scenario analysis via power query. The excellent part in its functionality is the ability to model up circular arguments where you have a production flow going down a process with capacity constraints going up the chain. being a bit selective about what loads through to a data model and taking care of what transformations occur in each part of the process, the model has a update time of 1:05 which I am pretty stoked with. The same type of scenario generation in Cognos is a setup time of around 3 days and an overnight run time.
    PQ really is extraordinarily powerful.

Leave a Reply


« »