fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

9 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.

  3. Senthil S says:

    Hi Chandoo,

    I got to see your youtube presentation on " 5 Tips - On Using powerquery for Accoutants. I am CA and was curious about Power Query. Happy that I got to see your presentation. Very Thoughtful with practical examples for beginners. Enjoyed it.

    Thank you,
    Senthil

  4. Arti viswanathan says:

    Hi Chandoo sir, I have one doubt I have learned excel, but I have stuck in one formal that IF and And formal when I put the formula in excel sheet it shows some error or n/a.... I am trying to learn it but it's not coming can pl teach it..... It would be greatful to u.... And in some companies they ask for writing letters in excel..... Can pl tell me how to write it..... Thank you....

  5. Chris Chitemerere says:

    Dear sir

    Have you ever created financial statements using general ledger transactions in Power BI. If so kindly share the PBIX file.

  6. Vasundhra says:

    Hi
    Could you please advise me an excel or BI course designed for Accounting professional

  7. Abid Mumtaz says:

    Hi Chandoo,
    I am regular follower of your blogs, just hoping if you could make a vedio on how add non financial table with financial table for example financial table includes revenue and cost information by hotel name and then add new table which have information like avaliable rooms, room sold, pax in restaurants. How can I link together these two tables and make further analysis such as margin on different days by hotel along with occupancy? Thank you

Leave a Reply