Power Query - Units per Month

[therealjdj]

Hello teams,
So thanks to some of you, I have been using Power Query. It's a fantastic tool. I have used some of your help and by reading and watching videos have been able to do what I was set out to do. I have just one issue left and I can not figure this one out. Hoping you guys can come to the rescue once again.

Let's say I am selling cookies. And after pulling a few sheets together I have information on my customer, how large his order is, when he wants the cookies, and how many cookies I can make for him max that month.
So if you order 5 cookies starting in August, but I can only make 3 a month for you, I would sell you 3 in August and 2 in September. The amount of cookies I make for you does not impact the other customers. So I could make a max of 3 cookies for as many customers as I want.

I need this data to be transformed into single lines showing sales per month so I can Pivot Table it.

Hoping you guys can help...

Book1
	A	B	C	D
1	Customer Name	Cookie Qty	Start Date	Max Cookies per Month
2	David	7	9/1/2023	3
3	Michael	1	8/1/2023	5
4	Marie	14	12/1/2023	3
5
6
7	Customer Name	Month	Year	Cookies
8	David	September	2023	3
9	David	October	2023	3
10	David	November	2023	1
11	Michael	August	2023	1
12	Marie	December	2023	3
13	Marie	January	2024	3
14	Marie	February	2024	3
15	Marie	March	2024	3
16	Marie	April	2024	2
Sheet1

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
    tbl1 = Table.AddColumn(tbl, "List", each 
        let 
            lst = List.Generate(()=> [Cookie Qty], (x)=> x > 0, (x)=> x - [Max Cookies per Month], (x)=> List.Min({[Max Cookies per Month], x})),
            lst1 = List.Accumulate({0..List.Count(lst)-1}, {}, (s,c)=> s & {Date.AddMonths([Start Date], c)}),
            lst2 = List.Transform(lst1, (x)=> {Date.MonthName(x), Date.Year(x)})
        in  
            List.Zip({lst2,lst})),
    tbl2 = Table.ExpandListColumn(tbl1, "List"),
    tbl3 = Table.SplitColumn(tbl2, "List", each _, {"DateList", "Cookies"}),
    tbl4 = Table.SplitColumn(tbl3, "DateList", each _, {"Month", "Year"}),
    Result = Table.RemoveColumns(tbl4,{"Cookie Qty", "Start Date", "Max Cookies per Month"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J
1	Customer Name	Cookie Qty	Start Date	Max Cookies per Month		Customer Name	Month	Year	Cookies
2	David	7	9/1/2023	3		David	September	2023	3
3	Michael	1	8/1/2023	5		David	October	2023	3
4	Marie	14	12/1/2023	3		David	November	2023	1
5						Michael	August	2023	1
6						Marie	December	2023	3
7						Marie	January	2024	3
8						Marie	February	2024	3
9						Marie	March	2024	3
10						Marie	April	2024	2
11
Sheet2

 

[therealjdj]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
    tbl1 = Table.AddColumn(tbl, "List", each
        let
            lst = List.Generate(()=> [Cookie Qty], (x)=> x > 0, (x)=> x - [Max Cookies per Month], (x)=> List.Min({[Max Cookies per Month], x})),
            lst1 = List.Accumulate({0..List.Count(lst)-1}, {}, (s,c)=> s & {Date.AddMonths([Start Date], c)}),
            lst2 = List.Transform(lst1, (x)=> {Date.MonthName(x), Date.Year(x)})
        in 
            List.Zip({lst2,lst})),
    tbl2 = Table.ExpandListColumn(tbl1, "List"),
    tbl3 = Table.SplitColumn(tbl2, "List", each _, {"DateList", "Cookies"}),
    tbl4 = Table.SplitColumn(tbl3, "DateList", each _, {"Month", "Year"}),
    Result = Table.RemoveColumns(tbl4,{"Cookie Qty", "Start Date", "Max Cookies per Month"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J
1	Customer Name	Cookie Qty	Start Date	Max Cookies per Month		Customer Name	Month	Year	Cookies
2	David	7	9/1/2023	3		David	September	2023	3
3	Michael	1	8/1/2023	5		David	October	2023	3
4	Marie	14	12/1/2023	3		David	November	2023	1
5						Michael	August	2023	1
6						Marie	December	2023	3
7						Marie	January	2024	3
8						Marie	February	2024	3
9						Marie	March	2024	3
10						Marie	April	2024	2
11
Sheet2

You guys are amazing!!!
Transferred it to my actual data set, made some modifications and we are good to go. THANK YOU!!!