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 l...
Need Help
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.