cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

## Power Query to Assign month number

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
)```
1 ACCEPTED SOLUTION
Super User

Hi @Anonymous ,

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

`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 🙂

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

4 REPLIES 4
Anonymous
Not applicable

thanks

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Sage

You can add a custom column with this expression.

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

Pat

Microsoft Employee
Super User

Hi @Anonymous ,

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

`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 🙂

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors