"Month Number" Formula

[jellis1971]

Hi:

In Power Query Editor, I have a column showing the names of months.  

How do I create a separate column showing the number for each month (i.e., "1" for "January", "2" for February, etc.)?

Thanks!

John

[Jay2022]

How would i amend this to continue on for additional years (eg count to 13 14 and so on for jan, feb the following year)

[v-henryk-mstf]

Hi @jellis1971 ,

Whether the advice given by @tackytechtom  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[jellis1971]

Unfortunately, I do not.

[tackytechtom · Accepted solution]

Hi @jellis1971 ,

That's a pity... since there is no M Power Query function to reverse MonthNames to Numbers... There is always the possibility for twelve if clauses I suppose, but here another one that you might consider sufficient:

Create a new custom column with the following code:

Date.Month ( Date.FromText ( "1 " & [MonthName] & " 2022" ) )

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlSK1YlWcktNKoJzfBOLkjPALMeCoswcqBhEzqs0LxXKyIGIOJamlxaXgJnBqQUlqblJqUVgnn9yST6M7ZdfhpBwSU2GcmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MonthName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MonthName", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MonthNumber", each Date.Month ( Date.FromText ( "1 " & [MonthName] & " 2022" ) ))
in
    #"Added Custom"

Does this work for you? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom]

Hi @jellis1971 ,

Does your table also contain a date column corresponding the month names? If so, you can use the Date.Month function on that column.

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/