Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Karjaasu
New Member

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

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 DateDurationMonthly Amount
01/12/2022150
01/20/202212400
02/04/2022160
02/10/20226700
04/15/20223650
05/29/202212300
03/31/20235450

 

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 DateDurationMonthly AmountRevenue Start DateRevenue End Date22m0122m0222m0322m0422m0522m0622m0722m0822m0922m1022m1122m1223m0123m0223m0323m0423m0523m0623m0723m0823m0923m1023m1123m12
01/12/202215001/01/202201/31/20225000000000000000000000000
01/20/20221240001/01/202212/31/2022400400400400400400400400400400400400000000000000
02/04/202216002/01/202202/28/20220600000000000000000000000
02/10/2022670002/01/202207/31/2022070070070070070070000000000000000000
04/15/2022365004/01/202206/30/2022000650650650000000000000000000
05/29/20221230005/01/202204/30/2023000030030030030030030030030030030030030000000000
03/31/2023545003/01/202307/31/20230000000000000045045045045045000000

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

1 REPLY 1
wdx223_Daniel
Super User
Super User

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.