Create dynamically columns based on data and populate values correctly in columns

[Karjaasu]

Hi,

I've tried to find a way to create dynamically new columns based on the available values.
My starting point is the following type of table:

Start Date	Duration	Monthly Amount
01/12/2022	1	50
01/20/2022	12	400
02/04/2022	1	60
02/10/2022	6	700
04/15/2022	3	650
05/29/2022	12	300
03/31/2023	5	450

Base on the years in the data, there should be a monthly column for each year.

Additionally, the Monthly Amount should be spread and populated in each correct, newly created month column.
The outcome should be like this:

Start Date	Duration	Monthly Amount	Revenue Start Date	Revenue End Date	22m01	22m02	22m03	22m04	22m05	22m06	22m07	22m08	22m09	22m10	22m11	22m12	23m01	23m02	23m03	23m04	23m05	23m06	23m07	23m08	23m09	23m10	23m11	23m12
01/12/2022	1	50	01/01/2022	01/31/2022	50	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
01/20/2022	12	400	01/01/2022	12/31/2022	400	400	400	400	400	400	400	400	400	400	400	400	0	0	0	0	0	0	0	0	0	0	0	0
02/04/2022	1	60	02/01/2022	02/28/2022	0	60	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
02/10/2022	6	700	02/01/2022	07/31/2022	0	700	700	700	700	700	700	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
04/15/2022	3	650	04/01/2022	06/30/2022	0	0	0	650	650	650	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
05/29/2022	12	300	05/01/2022	04/30/2023	0	0	0	0	300	300	300	300	300	300	300	300	300	300	300	300	0	0	0	0	0	0	0	0
03/31/2023	5	450	03/01/2023	07/31/2023	0	0	0	0	0	0	0	0	0	0	0	0	0	0	450	450	450	450	450	0	0	0	0	0

[wdx223_Daniel · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY6xDcAwCAR3obYEPMZRZrG8/xoxVkBJAcU9p2dOEmUFQwBqpHtcaLXDIcVjdXkTsPSvMYprGWPPlUJn9QwswuzY9P51WCrGFg8gzj3KQ1kP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, Duration = _t, #"Monthly Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"Duration", Int64.Type}, {"Monthly Amount", Int64.Type}}),
    Custom1 = #table(Table.ColumnNames(#"Changed Type")&{"Start","End","x","y"},List.TransformMany(Table.ToRows(#"Changed Type"),each List.Transform({0.._{1}-1},(x)=>Date.ToText(Date.AddMonths(_{0},x),"yyyy\mMM")),(x,y)=>x&{Date.StartOfMonth(x{0}),Date.EndOfMonth(Date.AddMonths(x{0},x{1}-1)),y,x{2}})),
    Custom2 = Table.Pivot(Custom1,List.Distinct(Custom1[x]),"x","y")
in
    Custom2