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
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