• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dummy Bank Loan Ledger / Statement of Account in Power Query

CMMaliniJoshi

New Member
Dear All

I am new to power query and for the last few days I have been trying to prepare a dummy ledger in power query based on given transactions data. However, I am unable to achieve the desired results as I cannot guess which functions should I use and how should I use. The data for a particular case is as under:
1. Date (Date of transaction)
2. Particulars (Narration of transaction)
3. Debit Amount (Amount of transaction)
4. Credit Amount (Amount of transaction)
Details of loan:
5. Loan Start Date
6. Loan Amount
7. Interest Rate
8. Ledger Required Till Date.
Based on above information, I need to prepare a ledger like statement in which I have to calculate daywise Interest and at the end of each month I have to debit the interest amount in the account. I started with creating Closing Balance column with the help of list.generate by adding Debit Amount in previous day's Closing Balance Amount and deducting Credit Amount from that figure. But I even could not achieve that figure correctly. I could prepare that ledger in Excel, but I am trying to prepare it in Power Query but in vain. Hence, I request you all to help with a solution for the given requirement.
For the purpose, I am enclosing herewith Sample Data in an excel file. It contains details as under:
1. Sheet Named "Input": It contains the details of Loan and sample data of transactions.
2. Sheet Named "Output": It contains the output I am trying to achieve. The first entry's date and amount is based on loan details given for the case. While calculating days for interest, when there is an entry for Interest, I have to included that day, too, when calculating the interest. But for rest of the transactions, number of days are calculated just by deducting date from next transaction date. The columns for which I have marked in red "Not required in Output" are the columns which I created for calculation purpose only, they are not required in final output.
I tried to find on internet solutions for such requirement but could not find any. However, if anyone can show me if any such solution is available then it will be helpful too.
I request you to help me with a solution.
Regards
 

Attachments

  • Ledger.xlsx
    15.1 KB · Views: 6
Dear All
I have been trying to achieve desired output in power query but i could not. Therefore i request all if anyone can help with a DAX solution if possible.
Regards
 
Dear All

Further to my first post, I had been trying for a solution in PQ. After posting the query a new requirement had been added. The interest rate during the period of statement keeps changing, hence, I have added a table named ROI_History in the excel file. Based on all the inputs, I was able to create a dummy ledger like statement in PQ as per excel file attached. The output achieved through Power Query is named Output_PQ. The output is as per my requirement, but the solution prepared by me works slow. One more issue I am facing in this solution is that, sequence of rows gets changed in final output in some rows. For example, according to the input table (named "Trn_Data") for transactions dated 01.03.2015, transaction of Rs 28000 comes first and that of Rs 4000 comes next. However, in the final output it appears inverse i.e. Rs 4000 comes first and Rs 28000 comes next.

Hence, I shall be thankful if any one can give me an idea to improvise this solution to make it faster and smaller and how to handle the issue of wrong sequence.

Regards
 

Attachments

  • Ledger.xlsx
    34.6 KB · Views: 5
Dear p45cal

Very nice... It worked.... now the sequence is correct. Can you please guide what was wrong in there and how Table.Buffer helped to correct it? I shall also be thankful if you can advise / guide / suggest on my other requirement as under:

Hence, I shall be thankful if any one can give me an idea to improvise this solution to make it faster and smaller (Repeated text of earlier post)

Regards
 
Back
Top