Inserting new rows in Power Query report

Anwar

New member
Joined
Jul 27, 2015
Messages
2
Reaction score
0
Points
0
I need to reconstruct high volumes of bank statements and calculate interest on the monthend balance. I have the acc nrs, transaction dates and transaction amounts to perform the interest calculation and I've designed a query that produced the transaction -dates and -amounts in a table. I've expanded the table with additional columns to calculate the nr of days, linked the interest rate table and calculate the monthend closing balance. From these 3 fields I can calculate the monthly interest by way of an Excel formula in the table. My problem is how to insert new rows on the bank statement table to firstly indicate the start date and secondly to do the interest calculation as there are no interest calculations in my source data. By way of an example; the statement start date may be 1 Jan 2011 and interest is calculated montly on the 1st of the following months and I'll need additional rows with transaction dates on the 1st of each month to perform the interest calculation for that date. The first interest date in this example will be 1 Feb 2011 and then 1 March 2011 etc until the 1st of the current month.

The start date and interest calculation dates may also differ between account types. On some accounts, interest may be calculated monthly on the 1st and on others monthly on the 25th. Interest can also be calculated and capitalised annually as on the attached example. On the attached example I manually inserted the rows highlighted in yellow in the source data but it's not ideal to do it for hundreds of accounts.

Can this be solved by way of a Power Query formula?
 

Attachments

  • Source data.xlsx
    14.6 KB · Views: 33
  • Example.xlsx
    29.1 KB · Views: 49
Back
Top