Results 1 to 1 of 1

Thread: Inserting new rows in Power Query report

  1. #1

    Inserting new rows in Power Query report

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts