Forum Discussion

Karjaasu's avatar
Karjaasu
New Member
2 years ago
Solved

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 Date Duration Monthly Amount 01/12/202...
Show and Tell
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago
    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
wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
2 years ago
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