moving year table to month based table

[Winstoncore]

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 

[Anonymous]

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.

[AlienSx]

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