Power Query to Assign month number

[Anonymous]

Hi Expert

How could you do the following in POwer Query as my FACT TAble has a million rows

MonthNo = SWITCH('Year - Month'[Month], 
        "Jan", 1,
        "Feb",2,
        "Mar",3,
        "Apr",4,
        "May",5,
        "Jun",6,
        "Jul",7, 
        "Aug",8,
        "Sep",9,
        "Oct",10,
        "Nov",11,
        "Dec",12
                )

[Anonymous]

thanks

[Ashish_Mathur]

Hi,

This ideally should be with DAX in your Calendar Table (where you certainly not have millions of rows).

[ppm1]

You can add a custom column with this expression.

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

Pat

[tackytechtom · Accepted solution]

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/