Forum Discussion

Winstoncore's avatar
Winstoncore
New Member
2 years ago

moving year table to month based table

hi guys, i have this table with only year for every target and i need to make the target based on monthly target 
thats say i need to add month coulmn to this table or just 01/01/2010 , 01/02/2010 like this for all 12 month in the year and than divide the target column to 12 and than getting to monthly target for each reseller 

i tried a lot of way to this ,ayne u can help me with that 

 

Need Help

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    hello, Winstoncore 

    let
    Source = your_table,
    months = {1 .. 12},
    dates = List.Buffer(List.Transform(months, (x) => #date(2010, x, 1))),
    add_dates = Table.AddColumn(Source, "Month", each dates),
    mod_target = Table.TransformColumns(add_dates, {"Target", each _ / 12}),
    fnl = Table.ExpandListColumn(mod_target, "Month")
in
    fnl
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Winstoncore 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFDSUTK2NAeSRkbGBgZKsTpwYRMLEGloChKNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, ResellerKey = _t, Target = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"ResellerKey", Int64.Type}, {"Target", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Numbers(1,12)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each [Target]/12)
in
    #"Added Custom1"

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.