Budget Allocation M Code

Power Query M Code Showcase
1

Hi there!

I wanted to split the yearly Budget Amounts into months to then multiply each month by its respective budget allocation weight %.

To that end I used the following code that I found on community.fabric:

= Table.AddColumn(#“Renamed Columns”, “Custom”, each Table.AddColumn(#“Renamed Columns”, “Custom”, each List.Transform({0…12-1}, (x) => Date.AddMonths(#date(2021,01,01),x))))

However, the problem I’m having is that this piece of M does not recognize the year, e.g. it returns me 2021 dates for year 2022, and would need this code to recognize the year so it can be then return the right date. Once the right date is obtained, next steps is to merge queries with Budget_Weight table to pull the monthly budget % distribution to apply to each month.

How could I write the code in such a way that would allow different yearly % distribution. Say for instance we now just have % weight January through December but imagine this changes to Jan (Year) through Dec (Year)

image
image851×403 34.2 KB

image

Thanks a bunch TEAM
MyWorkingsWIP.pbix (1.1 MB)

2

Hi @SamSPAIN,

You didn’t include data file…

First instead of a fixed date #date(2021,01,01) just sub in the Year column.

Table.AddColumn(
    #"Renamed Columns", 
    "Custom", 
    each List.Transform({0 .. 11}, (x) => Date.AddMonths(#date([Year], 1, 1), x))
)

To obtain the monthly weight %, add a temporary column with month name to create a join.

Date.MonthName([Custom], "en-US")

I hope this is helpful

1 Like
3

Hi @Melissa, thanks a bunch for your message.

I copied the M code you provided and then Merged the Budget table with the Budget_Weights which I ultimately unloaded to the model - here attached the PBIX.

One thing though, imagine the Budget_Weights business inputs changes (which typically happens) and it comes in a different format. Say for instance we have monthly weights different for different years, would your suggestion to add this temporary column work?

Lastly, I had to connect (inactive relationship) Budget with Regions using City as linkage - would you foresee any major downside using this approach?

Much appreciate :slight_smile:
MyWorkingsWIP.pbix (1.2 MB)

image

4

Hi @SamSPAIN,

I can imagine a lot of things :wink:

But again - as you know - without a separate data file, I can’t review anything in the Power Query Editor of the provided PBIX file. Furthermore I feel we are moving from a PQ question to a modelling one. When you provide a data file, I’ll have another look.

Cheers.

1 Like