duplicating entries for every year until the end date

[ajm]

i am mapping out contracts in power query. each contract is entered into the system and effort (labour) is apportioned to it. the user has asked that i add in the effort taken to review each valid contract annually. there is a formula used to calculate each review date out until the last review prior to contract end date. for each review, i need to create a new entry in the table.

as an example, contract number AB456321 starts 25/12/2023 and ends 24/12/2030. with all conditions satisfied for a review, the review dates would be 25/12/2024, 2025, 2026, 2027, 2028, 2029. the end result in my table would then be 7 entries: one original contract and 6 review exercises.

Any help would be appreciated as I am googling up a storm and getting nowhere fast this morning.

 

[alansidman]

Show us a sample of what your data looks like now and what you would like it to look like after PQ is applied. Please use XL2BB and no pictures as they cannot be manipulated.

 

[ajm]

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M
3	Contract Owner	Contract	Description	Parties	End Date	Business Need	Contract Status	Start Date	Section	Next Activity	Contract Length (Years)
4	Me	AJM000088	Software App	Software Company	26-Sep-2027	Ongoing	Approved	27/09/2017	Goods & Services	Extend	10		EXISTING
5	Me	AJM000088	Software App	Software Company	26-Sep-2024	Ongoing	Approved	27/09/2017	Goods & Services	Price & Performance Review	10		EXISTING
6	Me	AJM000088	Software App	Software Company	26-Sep-2025	Ongoing	Approved	27/09/2017	Goods & Services	Price & Performance Review	10		NEW
7	Me	AJM000088	Software App	Software Company	26-Sep-2026	Ongoing	Approved	27/09/2017	Goods & Services	Price & Performance Review	10		NEW
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M4:M7	Cell Value	>5	text	NO

the conditions that need to be met for a Price and Performance review to be set are Business Need is Ongoing and Contract Status is Approved.

the original contract is in the first row with the Price and Performance Review set on the anniversary of the start date each year. this continues until the last year of the contract. At the moment, only the next PPR is included (these are the records marked EXISTING in the last column above). As this particular contract runs until 26 Sep 2027 and the next review is set for 2024. i need also to be able to list the next PPR dates in 2025 and 2026.

 

[ajm]

anyone?

it would even help if i knew what to search for on the internet. using "insert rows" generally lists a bunch of entries about manually inserting a row. i need to figure out a way to trigger the insertion.

 

[ajm]

list.generate seems to help. but i need the dates to increment each year until the expiry year.

 

[Automatrix]

This will give you a list of the years involved, then you can expand it into new rows and add days and months afterward. Is that helpful?

{Date.Year([Start Date])..Date.Year([End Date])}

 

[ajm]

Thanks automatrix. I used

= Table.AddColumn(#"Added Custom", "Future PPR", each let startDate = Date.AddYears([EndDate],1), endDate = [EndDateOriginal] in List.Combine({{[EndDate]}, List.Generate(()=> startDate, each _ <= endDate, each Date.AddYears(_, 1))}))

i now need to add a condition to the above that will not return a date when the "Future PPR" date is less than 12 months from the End Date. EG so if the contract start date is 1 Jan 2020 and its end date 31 July 2024, the final PPR should be 1 Jan 2023, not 1 Jan 2024.

any ideas?