Partners,,,, russianxnxx

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!

Clean up Trial Balance in Power Query - Data

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!

Clean up Trial Balance in Power Query - Output Expected



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


    ⬇️ Pop in your Name & Email to get the file!


    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

    1. Consistent Selling Products – DAX
    2. Extract first and last names – Power Query
    3. Calculate Upgrades – DAX
    4. Find Max Date Value – Power Query
    5. Growth from Last Non Consecutive Date – DAX


    Topics that I write about...