How to assign month number to each month

[Niels_T]

How do you simply add a month number to each month?

For example: August = 08, October = 10, December = 12, etc.

[amitchandak · Accepted solution]

@Niels_T , Create a new column like

Switch( True() ,
[month of year] = "August" , "08",
[month of year] = "October" , "10",
[month of year] = "December " , "12",
//add others
)

refer: https://www.youtube.com/watch?v=gelJWktlR80

[v-eqin-msft · Accepted solution]

Hi @Niels_T ,

According to my understanding, you want to convert month name to number simply.Please follow these steps in Power Query as shown below:
Add custom column-->Change data type-->remain only Date.Month

Here is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixNLy0uUYrViVbyTy7JT0otArNdUpNTc2Ecv/wyBCc4taAEwfMqzamEMvJSwQzfxEooXZScAWY5FhRl5kAUJeaVJhZB5N1Sk4ognFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month of Year" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Month Number", each [Month of Year]&" 1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Month Number", type date}}),
    #"Extracted Month" = Table.TransformColumns(#"Changed Type",{{"Month Number", Date.Month, Int64.Type}})
in
    #"Extracted Month"

Please take a look at the pbix file here.

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

[Syndicate_Admin]

A good simple solution, works for me, thanks!

[littlemojopuppy]

Create a new column and use the MONTH() function

[Niels_T]

This unfortunately also doesn't work. it gives me this message:

I think that it has to do with my months of year's data type not being a date but instead text. I then tried to change the months of year into a date but this gives an error as well.

[amitchandak · Accepted solution]

@Niels_T , Create a new column like

Switch( True() ,
[month of year] = "August" , "08",
[month of year] = "October" , "10",
[month of year] = "December " , "12",
//add others
)

refer: https://www.youtube.com/watch?v=gelJWktlR80

[Niels_T]

Hello,

I get this message:

[amitchandak]

@Niels_T , remove comma at last. I kept it so that you can add more argument 

Switch( True() ,
[month of year] = "August" , "08",
[month of year] = "October" , "10",
[month of year] = "December " , "12",
1
)