Forum Discussion
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
)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/
4 Replies
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/Hi,
This ideally should be with DAX in your Calendar Table (where you certainly not have millions of rows).
You can add a custom column with this expression.
= Date.Month(Date.FromText("2022-" & [Month] & "-1"))
Pat
