Create 12 Monthly Budget columns in Power Query

[hananak]

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

 

[JGordon11]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Annual Budget		Annual Budget	January	February	March	April	May	June	July	August	September	October	November	December
2	12000		12000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000
3
Sheet1

 

[hananak]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Annual Budget		Annual Budget	January	February	March	April	May	June	July	August	September	October	November	December
2	12000		12000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000
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?

 

[JGordon11]

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.

 

[hananak]

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.

 

[JGordon11]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Annual Budget	M1A	M2A	M3A	M4A	M5A	M6A	M7A	M8A	M9A	M10A	M11A	M12A
2	24000	983	460	412	933	0	0	0	0	0	0	0	0
3	36000	917	127	154	869	0	0	0	0	0	0	0	0
4	12000	396	982	334	949	0	0	0	0	0	0	0	0
5
6
7	Annual Budget	M1A	M2A	M3A	M4A	M5A	M6A	M7A	M8A	M9A	M10A	M11A	M12A	M1B	M2B	M3B	M4B	M5B	M6B	M7B	M8B	M9B	M10B	M11B	M12B
8	24000	983	460	412	933	0	0	0	0	0	0	0	0	2000	2000	2000	2000	2000	2000	2000	2000	2000	2000	2000	2000
9	36000	917	127	154	869	0	0	0	0	0	0	0	0	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000
10	12000	396	982	334	949	0	0	0	0	0	0	0	0	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000
11
Sheet1

 

[hananak]

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.

 

[JGordon11]

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

 

[hananak]

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.

 

[JGordon11]

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)