Arrange Months in order in Direct Query

[Shree_185]

Hi everyone,

I am trying to order months text field in ascending order. As I am using Direct query, it's not allowing me to Order Months.

Any suggestions

[Anonymous]

Hi @Shree_185 ，

I tested using the SWITCH to create a new index column for the month, but I got an error in Sort by Column because the calculated column is derived from the MonthName column and is now used to determine the order.

Column = 
SWITCH(
    TRUE(),
    'Table1'[MonthName]="January",1,
    'Table1'[MonthName]="February",2,
    'Table1'[MonthName]="March",3,
    'Table1'[MonthName]="April",4,
    'Table1'[MonthName]="May",5,
    'Table1'[MonthName]="June",6,
    'Table1'[MonthName]="July",7,
    'Table1'[MonthName]="August",8,
    'Table1'[MonthName]="September",9,
    'Table1'[MonthName]="October",10,
    'Table1'[MonthName]="November",11,
    'Table1'[MonthName]="December",12,
    BLANK()
) 

When we are in DirectQuery mode and we don't pre-index fields in text type, fields are sorted alphabetically by default. If you don't want to change the connection mode, you can create the index for the MonthName column in the data source.

If you can change the connection mode from DirectQuery to Import, TheoC provides a good way.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[TheoC]

Hi @Shree_185 

You need to add a column in Power Query that converts the MonthName to a month number. You can use a Conditional Column (Ribbon: 1. Add Column, 2. Condition Column ) to achieve this or, if you have a Date column, you can use Add Column, then click on Date button, then Month, then Month again.  You can then sort by the numeric month value.

Hope this helps.
Theo

[BamaBonner76]

That doesn't work in DirectQuery where this is no table.