Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

Hi there,

 

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

vmzandonadi_0-1612362841182.png

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!

1 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

Jimmy801_0-1613111087788.png

Result

Jimmy801_1-1613111098945.png

 

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

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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

Table:

d1.png

 

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
)

d2.png

 

Result:

d3.png

 

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
Super User
Super User

@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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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

 

I have this table:

vmzandonadi_0-1612984276911.png

 

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:

vmzandonadi_1-1612984418222.png

 

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

vmzandonadi_2-1612984502041.png

 

Thanks!

 

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

Jimmy801_0-1613111087788.png

Result

Jimmy801_1-1613111098945.png

 

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
Not applicable

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

@Anonymous  did you try the above?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors