Create List of Dates at Monthly intervals Between Two Dates

rwilson
Posts: 27
Joined: Fri Jun 20, 2014 10:56 am

Create List of Dates at Monthly intervals Between Two Dates

Postby rwilson » Thu May 24, 2018 5:30 pm

Hello All

I have a table of leases with Lease ID, Start Date, End Date, Monthly Payment which I would like to convert in to larger table that shows every monthly payment for each lease commencing from one month after the Start Date to the End Date.

Any ideas on how this might be done in PQ?

rwilson
Posts: 27
Joined: Fri Jun 20, 2014 10:56 am

Re: Create List of Dates at Monthly intervals Between Two Dates

Postby rwilson » Fri May 25, 2018 2:05 pm

So . . . have been doing a bit of hunting around on the Google machine and I think the List.Generate function in PQ is what I need, but I can't quite get it to work.

I have attached a sample file which hopefully better explains what I'm trying to do.

The query I have written seems to work to the "Added Custom" step the falls over at the "Expanded DueDate" step with the following error.

Expression.Error: The field 'b' of the record wasn't found.
Details:
a=23/06/2016

I suspect the problem is a mismatch in the day number between the Start Date and End Date but not sure.

Any thoughts?

Thanks
Attachments
Lease Power Query Example.xlsx
(22.53 KiB) Downloaded 16 times

jiny680
Posts: 5
Joined: Thu Oct 27, 2016 7:10 pm

Re: Create List of Dates at Monthly intervals Between Two Dates

Postby jiny680 » Fri May 25, 2018 9:31 pm

is it right
Attachments
Lease Power Query Example.xlsx
(34.65 KiB) Downloaded 14 times

rwilson
Posts: 27
Joined: Fri Jun 20, 2014 10:56 am

Re: Create List of Dates at Monthly intervals Between Two Dates

Postby rwilson » Mon May 28, 2018 10:54 am

Hi jiny680.

Thanks for your response. Your answer is close but not quite right.

The due date for each lease payment is not the month end or "End Date" in your Answer table. For example, the first payment for Lease1 of $576.09 is due on 23/5/2016 which is then followed by 59 further payments of $576.09 due on the 22nd of each subsequent month ending on 22/4/2021.

Cheers

rwilson
Posts: 27
Joined: Fri Jun 20, 2014 10:56 am

Re: Create List of Dates at Monthly intervals Between Two Dates

Postby rwilson » Mon May 28, 2018 1:05 pm

OK . . . so I found some time to have another look at this and I think I might have cracked it - see updated file attached.

Note that I picked up a couple of errors in the source data table (not critical, just incorrect lease end dates) which I have now corrected.

Would appreciate any thoughts / comments re alternative / better solutions.

Cheers
Attachments
Lease Power Query Example-Updated.xlsx
(34.56 KiB) Downloaded 14 times

jiny680
Posts: 5
Joined: Thu Oct 27, 2016 7:10 pm

Re: Create List of Dates at Monthly intervals Between Two Dates

Postby jiny680 » Mon May 28, 2018 6:23 pm

ok
Attachments
Lease Power Query Example-Updated.xlsx
(64.19 KiB) Downloaded 14 times


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 3 guests

cron