The dataset has yearly opening balance and their monthly transactions for different items.
Need to derive monthly closing balance of various items and ledgers.
To derive the closing balance of the first month say; April, you need to create a new custom column by summing up the opening balance.
Below is the AddColumn panel through which we can add a custom column through custom expression to derive the Closing balance of every month.
For getting closing amount of April month we have summed up opening balance and monthly transaction of April.
Below is the expression which is used to fulfil our requirement of getting closing balance of every month.
Similarly, for every month sum up the closing balance of previous month and transaction of that particular month.
Next, for getting the Opening Balance for every month we need to copy closing balance column of previous month and rename it as opening balance column for current month.
For example: - Closing Balance of April will be considered as Opening Balance for May.
To merge the opening balance and closing balance, you need to do some transformations as below.
As a first step,
Change the data type of all the opening balance and closing balance from integer type to string type.
We are converting the data type to string so that we can do the transformation operation like concatenation which we are doing in next step.
Now concatenate the balance of every month with their respective month name.
We are concatenating the opening and closing balance with their respective month name so that we can differentiate between the balance amounts of different months.
For this right click on opening/closing balance column -> Transform -> more -> select “concat” operation.
Below is the resultant dataset after concatenating the opening balance and closing balance with their respective months.
Also you can delete the unwanted columns.
Now merge the opening balance and closing of each month separately with the help of a separator (like ‘:’).
We are merging these columns so that we can get opening and closing balance of each month in one column.
Syntax used: - Opening Balance:Closing Balance
Below is the dataset after merging the opening balance and closing balance of all the months.
Now merge all the above merged columns into a single column with a different separator (like ‘;’).
We are merging again so that we can get the opening balance and closing balance of all months into a single column.
Before merging all merged columns
After merging all Merged Columns
4000Apr:3000Apr; 4500May:5000May; 6000Jun:55000Jun
3000Apr:4500Apr; 3400May:3900May; 4000Jun:4300Jun
Now split the column as per the separator to pivot the opening balance and closing balance into column.
We need to split by the semi colon (;) first and then by the colon (:).
To achieve this right click on all merged column -> Split -> Spilt to row (separator ‘;’).
Below is the result dataset that we will get after splitting.
Now split column by the colon (:).
To achieve this right click on all merged column -> Split -> Spilt to column (separator ‘:’).
Below is the resultant dataset that we will get after splitting the column.
We need to split the column of opening balance and closing balance to dissociate the month name into separate column.
We are splitting month name from the opening and closing balance amount so that we can get a separate column of month name in front of its opening and closing balance.
To achieve this right click on all merged column -> Split -> Spilt to column (length 3 from right).
After splitting months from both columns you will get two month columns, so delete anyone of it and keep single month column to get the required dataset.
Below is the final dataset where have achieved the result required as of month and opening/closing balance is single column each.