Recently while on a consulting assignment, I faced an interesting problem on cleaning up Trial Balance Data using Power Query. The problem was interesting enough that it found a way to make it up for a Power Query Challenge!
Let’s start.
A Quick Video!
Here is a mocked up Trial Balance!
It’s not necessary to know the accounting technicalities to solve this problem! You can see that we have 4 columns..
- GL Code (GL Stands for General Ledger)
- GL Item (Name of the Ledger)
- The Balance of the CY (Current Year Balance i.e. Month Ending Feb 2020)
- And Balance of LY (Last Year Balance i.e. Month Ending Feb 2019)
The Problem
You have to transform and combine multiple Trial Balances (excel files) which are given in the above format into the following format with 4 columns – GL Code, GL Item, Period (Month Ending Date), and Balance.
Like this!
Rules
- There are multiple excel files (containing a trial balance each).
- All Trial Balances need to be cleaned and combined.
- Use Power Query to solve this problem.
Post a snapshot of the result and the M Code in the comments! The solution and and a shoutout to your guys in the next week. Get to it!
Solution Video
More DAX and Power Query Challenges
- Consistent Selling Products – DAX
- Extract first and last names – Power Query
- Calculate Upgrades – DAX
- Find Max Date Value – Power Query
- Growth from Last Non Consecutive Date – DAX
Automate repetitive data cleaning tasks using Power Query
A comprehensive course to learn Power Query to automate all your mundane and repetitive data cleaning tasks in Excel or in Power BI
DOWNLOAD THE COURSE OUTLINE | ENROLL IN THE COURSE
