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
- Connect to a file on computer
- Load bank transactions to a new Power Query connection
- Delete transactions of certain type
- How to categorize transactions by amount – small, medium or large txns
- How to duplicate a query
- Splitting transactions in to debits and credits
Combining monthly ledger data files to single table
In this section, learn how to
- Connect to a folder full of similar format files
- Set up sample query to teach Power Query how you want to merge files
- Combine the data and publish
- Create a pivot table report from the data
- Update the folder and refresh
- Look at modified pivot report
Reconciling Sent vs. Paid invoices
In this topic,
- What is reconciliation
- Loading tabular data in to Power Query
- Connecting two tables using “Merge queries” option
- Using left joins
- Removing duplicates
- Tagging invoices as either “Paid in full”, “Under paid”, “Over paid” or “Unpaid” using conditional column feature
- Publishing results
Live exchange rate tracker
In this section, learn how to:
- Connect to public websites to fetch currency data
- Use Reserve Bank New Zealand’s currency rate data as connection
- Reshaping data with “Transpose” and “Use headers as first row” options
- Removing un-necessary columns
- 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?
- Go to Data Ribbon
- Click on “Show Queries”
- Right click on any query and “Edit”
How to refresh the queries?
- Go to Data Ribbon
- Click on “Refresh All”
- Done.
I get an error when refreshing…
- The queries are mapped to local folder files
- So you need to re-map them to folders on your computer
- Edit the query with error (see steps above)
- Go to Source step
- Click on gear icon
- Change file address as per your computer
- 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.
9 Responses to “5 tips: Power Query for Accountants (and finance people) – Free Masterclass”
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?
It is available now in youtube.
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.
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
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....
Hi Arti... Can you please tell me a bit more on what you are doing? N/A error could mean various things... For reference on Excel errors, check this page - https://chandoo.org/wp/excel-formula-errors/
Dear sir
Have you ever created financial statements using general ledger transactions in Power BI. If so kindly share the PBIX file.
Hi
Could you please advise me an excel or BI course designed for Accounting professional
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