Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rarora20
Regular Visitor

Sorting by Month Number

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!

 

errore.pngerroree.png

1 ACCEPTED 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.

View solution in original post

13 REPLIES 13
pxg08680
Resolver III
Resolver III

@rarora20

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!

@rarora20

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.

 

a1.PNG

After right clicking I dont see the month--> month option. 

@rarora20

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

Can you share your data..

@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.

@parry2k You just hit all the right chords!! Thanks a ton!! Cheers!

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).

Hasan
Resolver I
Resolver I

  • select the data tab
  • select the column: Month - makse sure that month is your defult
  • "Sort By Column": Number"

Tried it. Still giving me the same result. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.