How to divide a whole value of year in columns and rows within Power Query

[Anonymous]

Hi there,

I have values that refer to the whole of 2020 year, like as image below:

And I would like to divide these values into:

1. 12 COLUMNS that will divide the value of the TotalCost column by 12, which will represent my monthly value.

2. I'd like to do the same as above, but instead of creating 12 columns, I'd like to create 12 ROWS, with the respective monthly values.

PS: I know I could do one of the two ways and then pivot or unpivot the columns, but I would like to learn the M code for each case.

I appreciate your help with that!

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a custom column with following m codes like below.

Value.Divide(
    List.Sum(
        Table.SelectRows(#"Changed Type",(x)=>x[Year]=[Year])[Value]
    ),
    12
)

Result:

Best Regards

Allan

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

[smpa01]

@Anonymousplease try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUTI0MDBQitWB843R+EZgfiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"sum", each List.Sum([Column1]), type nullable number}}),
    #"Divided Column" = Table.TransformColumns(#"Grouped Rows", {{"sum", each _ / 12, type number}}),
    #"Added Custom" = Table.AddColumn(#"Divided Column", "Custom", each {1..12}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Column1", "Custom", "sum"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "year"}, {"sum", "value"}})
in
    #"Renamed Columns"

[Anonymous]

Hi @smpa01 I'm sorry for late, I will try to explain better.

I have this table:

I'd like to get these 2 results using M language:

1) Divide the TotalCost (image above) by 12 months and create the columns for each month, like this:

2) Instead create columns for months, I want to create rows for each month but also using M language:

Thanks!

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you can try this approach. Use Table.TranformColumns to go through your TotalCost-Column and create of every single value a table with 12 rows and the TotalCost-Amount / 12. After this transformation you can expand the created table. 

Here the transformation of the Table.TransformColumns-function

{"TotalCost", (tc)=> Table.AddColumn(Table.FromColumns({List.Numbers(1,12)}, {"Month"}), "Monthly cost", (add)=> tc/12) , type table}}

 and here a complete code example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUTI0MjAwUIrVgQsYmaAJGJuhCZhYgAViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, TotalCost = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"TotalCost", type number}}),
    Split12 = Table.TransformColumns(#"Changed Type",{{"TotalCost", (tc)=> Table.AddColumn(Table.FromColumns({List.Numbers(1,12)}, {"Month"}), "Monthly cost", (add)=> tc/12) , type table}}),
    #"Expanded TotalCost" = Table.ExpandTableColumn(Split12, "TotalCost", {"Month", "Monthly cost"}, {"Month", "Monthly cost"})
in
    #"Expanded TotalCost"

Before

Result

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi @Jimmy801 it will work for me, thanks for your help!

[smpa01]

@Anonymous  did you try the above?