Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to 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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
18 | |
10 | |
9 | |
9 | |
8 |
User | Count |
---|---|
17 | |
16 | |
14 | |
12 | |
12 |