Power Query to Assign month number

Hi Anonymous ,

There is no switch function in Power Query. You need to use a nested if logic. Here a solution:

Here the M code to start with:

if [Month] = "Jan" then 1 else if [Month] = "Feb" then 2 else if [Month] = "Mar" then 3 else if [Month] = "Apr" then 4 else if [Month] = "May" then 5 else null

To be honest, the first thing I asked myself when I read your question was whether you have a date dimension. Typically, you do not want to do such things on a fact table. Instead your fact table should have a foreign key to a date dimension where you then have all these attributes like Month, Monh No, Day, IsWeekDay, Quarter, Year etc. Also with a date dimension you can use all these powerful and in-built time intelligence functions like YTD, MTD etc. So, if you have not checked this out yet, I recommend to first look into this before doing transformations on the fact table itself.

Let me know if this helps 🙂

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