Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a month and year column. I have created a conditional column which gives me the month number based on the month name. When I try to sort the data by the conditional column(Number), it gives me the month names as Jan Oct Nov Dec Feb Mar Apr May Jun Jul Aug Sept instead of the ascending month names. Please help. Thank you!
Solved! Go to Solution.
@rarora20 make sure to change date type for your new column M# to whole number, it is string , you will not get correct sort because with string sort will go like this
1
10
11
12
2
3
4
5
6
7
8
9
and that is the reason you see Jan, Oct, Nov, Dec and so on..
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Why doing such a long process. Instead try doing this.
Goto queryeditor, duplicate your month column and transform to MonthNumber and rename it any name and apply.
Now in modeling tab , use this new named column to sort the month column.
@pxg08680 how do I transform to month number. I am new to this. Help me with some steps. I have a month name column. Thank you!
Do these steps
STEP1: Goto Home, EditQueries.
STEP2: Select the Month column that you have, right click and goto transform-->Month-->Month. This gives you month numbers.
STEP3: For same column right click and rename it to MONTHNUMBER.
STEP4: Close& Apply.
STEP5: Select the Month column from your data and got to modeling tab, sort column, select the newly created column, sort by MONTHNUMBER.
This should work.
After right clicking I dont see the month--> month option.
I am sorry, I was thinking that your data has a date column. What I posted works for Date fields.
You can do a switch kind of statement.
STEP1: In EditQuery, duplicate your month column to M# or any name. Close & Apply.
STEP2: Now create a new column this way...
column = IF(Table1[M#] = "January",1,
IF(Table1[M#] = "Feb",2,
IF(Table1[M#] = "March",3,
IF(Table1[M#] = "April",4,
IF(Table1[M#] = "May",5,
IF(Table1[M#] = "June",6,
IF(Table1[M#] = "July",7,
IF(Table1[M#] = "August",8,
IF(Table1[M#] = "Sep",9,
IF(Table1[M#] = "Oct",10,
IF(Table1[M#] = "Nov",11,
IF(Table1[M#] = "Dec",12 ))))))))))))
Now sort you Month column with the newly create column.
Getting the same results. It's weird because if you look at the screenshots, after sorting only Oct Nov Dec are unsorted rest everything is sorted.
@rarora20 make sure to change date type for your new column M# to whole number, it is string , you will not get correct sort because with string sort will go like this
1
10
11
12
2
3
4
5
6
7
8
9
and that is the reason you see Jan, Oct, Nov, Dec and so on..
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Awesome, glad to head that issue is fixed 🙂
Cheers,
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
In the query editor, select your query, select your month (ie., number) column, right click, change type, click whole number.
You can do the same using Data Type as "whole number".
Basically, when you added conditional column, it shows Data Type as Any. You need to fix it as Whole number.
After this, Verify: Data Tab in the main app: Make sure for the month: january, feb ... column, sort by column is still the number column.
Save. Now the order should be good and recreate the chart (if it is not fixed).
Tried it. Still giving me the same result.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |