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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors