Dynamically add month columns and conditional values in PQ

[JGordon11]

How would one use PQ to transform the table on the left into the table on the right?

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item	Start	End	Amount		Item	February 2021	March 2022	April 2021	May 2021	June 2021	July 2021
2	1	2/1/2021	4/30/2021	50		1	50	50	50
3	2	3/1/2021	6/30/2021	100		2		100	100	100	100
4	3	4/1/2021	7/31/2021	75		3			75	75	75	75
Sheet1

 

[RoryA]

I'm sure there are better ways, but something like this should work:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Start", type date}, {"End", type date}, {"Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MonthList", each let dateFrom = [Start], dateTo = [End] in List.Generate(()=>dateFrom, each _ < dateTo, each Date.AddMonths(_, 1))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Start", "End"}),
    #"Expanded MonthList" = Table.ExpandListColumn(#"Removed Columns", "MonthList"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded MonthList", {{"MonthList", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded MonthList", {{"MonthList", type text}}, "en-GB")[MonthList]), "MonthList", "Amount", List.Sum)
in
    #"Pivoted Column"

 

[JGordon11]

Thanks - your Table.AddColumn step was where I was going to great and mostly unproductive lengths to try to accomplish.