Scenario:

The dataset has yearly opening balance and their monthly transactions for different items. 


Requirement: 

Need to derive monthly closing balance of various items and ledgers.












Step 1:

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.



Step 2:


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. 




Step 3:


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.



Step 4:


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.


Step 5:


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.


For Example:-

 

Merge_Apr

Merge_May

Merge_Jun

4000Apr:3000Apr

4500May:5000May

6000Jun:5500Jun

3000Apr:4500Apr

3400May:3900May

4000Jun:4300Jun


Syntax used: - Opening Balance:Closing Balance


Below is the dataset after merging the opening balance and closing balance of all the months.



Step 6:


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.

For Example:-

Before merging all merged columns


 

Merge_Apr

Merge_May

Merge_Jun

4000Apr:3000Apr

4500May:5000May

6000Jun:5500Jun

3000Apr:4500Apr

3400May:3900May

4000Jun:4300Jun

 

After merging all Merged Columns


Month_Bal_Merge

4000Apr:3000Apr; 4500May:5000May; 6000Jun:55000Jun

3000Apr:4500Apr; 3400May:3900May; 4000Jun:4300Jun




Step 7:


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 (:).


For Example:-


Before Splitting

After Splitting

4000Apr:3000Apr;4500May:5000May;6000June:5500June

4000Apr:3000Apr

 

4500May:5000May

 

6000June:5500June

 

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 ‘:’).



For Example:-


Opening_Balance

Closing_Balance

4000Apr

3000Apr

4500May

5000May

6000Jun

5500Jun

 

Below is the resultant dataset that we will get after splitting the column.



Step 8:


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.


For Example:-


Month

Opening_Balance

Closing_Balance

Apr

4000

3000

May

4500

5000

Jun

6000

5500

 

Below is the final dataset where have achieved the result required as of month and opening/closing balance is single column each.