Duplicating all rows a variable amount of times, and adding an extra column

[EvelynN]

I have a very specific issue that I'm finding hard to come up with a nice solution to.
I'm working with data which needs to follows tax years, so 1 "entry" runs from April 2021 to March 2022 for example.

I have a table of people with date ranges which need to be split into these years so for example I have:

	Start	End
Adam	Jun-20	Jan-21
Becky	Jun-21	Mar-24
Carl	Jun-20	Dec-24
Dolores	May-23	Jun-25

Which I would need to convert to:

	Start	End	Year
Adam	Jun-20	Jan-21	20/21
Becky	Jun-21	Mar-24	21/22
Becky	Jun-21	Mar-24	22/23
Carl	Jun-20	Dec-24	20/21
Carl	Jun-20	Dec-24	21/22
Carl	Jun-20	Dec-24	22/23
Dolores	May-23	Jun-25

(Assuming Todays date is the time I posted this question, 11-11-2022)

So the rules I need to incorporate are:

• Each person would need a seperate row for each financial year they are present
• A new column of "Year" is needed for each row where an entry for each financial year the person is present is created and correctly labeled
• Even if the end date is in the future, only have this happen up to the curren financial year

I have a background in Javascript and I can think of ways of doing this with for loops and the like, but I'm having trouble converting my logic into PowerQuery. I know how to do things like calculate the year based on todays date or the start date etc, but now how to multiply rows, and then dynamically do this for multiple rows where I add a different value into each row.

If anyone has any good ideas for starting points I would be greatly appriciative.

[Syndicate_Admin]

Good morning Syndicate Admin.

I'm interested in doing the same thing you're doing but at the month level, do you have at hand how you did it?

[ToddChitt]

If I understand you correctly:

Adam show up only once in the final result set because his Dates span only ONE tax year. But Becky's dates span multiple tax years (her START date is in 21/22 and her END date is in 22/23. 

I usually try to solve complex model issue like this in the source (Power Query) but I'm not sure that is possible. Is your back-end SQL? Can you write a complex view with GREATER THAN and LESS THAN statement on the JOIN clause?

[EvelynN]

Yeah you got the pattern of the data correct, things that have the same "start" date have different tax year spans, which is my problem.

Unfortunately not with editing the data before I see it in PBI, the system we're getting the data from is a picky thing which is very hard to manipulate within the tables themselves as they involve finance data.

So far I've managed to figure out a very long winded way of calculating how many tax years one entry is suppose to have by doing a few comparisons after extracting the start date month and year, but I'm no closer to figuring out the duplicating rows by a variable amount part.