Create 12 Monthly Budget columns in Power Query

hananak

Board Regular
Joined
Feb 10, 2022
Messages
110
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Dear All,

I was wondering if you could help.

I have an Annual Budget Column in Power Query. I want to divide it by 12 to get the monthly Budget and then have the result in 12 monthly columns using power query. I have done it using 12 steps. But is there any way to do this in 1 step?

Example:

Annual Budget : M1 - M1 - M2 - M3 - M4 - M5 - M6 - M7 - M8 - M9 - M10 - M11 - M12
12,000 : 1000 - 1000 - 1000 - 1000 - 1000 - 1000 - 1000 - 1000 - 1000 - 1000 - 1000 - 1000
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    months = List.Transform({1..12}, each Date.MonthName(#date(2023,_,1))),
    ab = Source[Annual Budget]{0},
    Result = Table.FromRows({{ab} & List.Repeat({ab/12},12)}, {"Annual Budget"} & months)
in
    Result

Book3
ABCDEFGHIJKLMNOP
1Annual BudgetAnnual BudgetJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
21200012000100010001000100010001000100010001000100010001000
3
Sheet1
 
Upvote 0
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    months = List.Transform({1..12}, each Date.MonthName(#date(2023,_,1))),
    ab = Source[Annual Budget]{0},
    Result = Table.FromRows({{ab} & List.Repeat({ab/12},12)}, {"Annual Budget"} & months)
in
    Result

Book3
ABCDEFGHIJKLMNOP
1Annual BudgetAnnual BudgetJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
21200012000100010001000100010001000100010001000100010001000
3
Sheet1
Thanks for providing the solution.

Is it possible to tweak the code slightly, as I do not want months name instead I need to have the below.

M1B, M2B, M3B,MB4 TIL M12B.

Also the data is already in PQ, I just need to reference to the specific column, pleaae advice how to refer to specific column in the above code?
 
Upvote 0
I would need to see an example of the starting data table and what you would want it to look like after performing the query.
 
Upvote 0
I would need to see an example of the starting data table and what you would want it to look like after performing the query.
Hi Gordon,

Please see the picture, which shows the data in PQ and the output I need.

Thank you for your help.
 

Attachments

  • Annual Budget.PNG
    Annual Budget.PNG
    198.7 KB · Views: 19
Upvote 0
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cols = List.Transform({1..12}, each "M" & Text.From(_) & "B"),
    lst = Table.ToRows(Source),
    lst1 = List.Transform(Source[Annual Budget], each List.Repeat({_/12},12)),
    lst2 = List.Transform(List.Zip({lst,lst1}), each List.Combine(_)),
    Result = Table.FromRows(lst2, Table.ColumnNames(Source) & cols)
in
    Result

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Annual BudgetM1AM2AM3AM4AM5AM6AM7AM8AM9AM10AM11AM12A
22400098346041293300000000
33600091712715486900000000
41200039698233494900000000
5
6
7Annual BudgetM1AM2AM3AM4AM5AM6AM7AM8AM9AM10AM11AM12AM1BM2BM3BM4BM5BM6BM7BM8BM9BM10BM11BM12B
82400098346041293300000000200020002000200020002000200020002000200020002000
93600091712715486900000000300030003000300030003000300030003000300030003000
101200039698233494900000000100010001000100010001000100010001000100010001000
11
Sheet1
 
Upvote 0
Solution
Thanks.
I tried your code but failed as not sure where to exactly put this code in the advanced editor and what to change.
I have uploaded the picture of the current M code in my PQ.
I would really appreciate if you could let me know, what to adjust in the above code and fit in my code at what place.
I am a beginner in PQ, so get really confused re M coding.
 

Attachments

  • M Code.PNG
    M Code.PNG
    153.9 KB · Views: 13
Upvote 0
Create a duplicate of your query, then work with the duplicate until it tests out ok.

Delete the last two lines of your code

in
#"Replaced Value"

and replace with this

cols = List.Transform({1..12}, each "M" & Text.From(_) & "B"),
lst = Table.ToRows(#"Replaced Value"),
lst1 = List.Transform(#"Replaced Value"[Annual Budget], each List.Repeat({_/12},12)),
lst2 = List.Transform(List.Zip({lst,lst1}), each List.Combine(_)),
Result = Table.FromRows(lst2, Table.ColumnNames(#"Replaced Value") & cols)
in
Result
 
Upvote 0
I did exactly the same but it's not working. It says unable to find "Annual Budget". Please see the uploaded image I have highlighted in yellow. In my code, it is appearing a bit different. I tried to put exactly the same as in it mentioned in my code but still not working.
 

Attachments

  • M Code.PNG
    M Code.PNG
    192.6 KB · Views: 8
Upvote 0
In your original query change the name of the appropriate column to Annual Budget. The add the steps to the end (but use the new last step name instead of #"Replaced Value" in the three lines of code)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top