Converting abbreviated Month(Text) to Month(Date Data type) Format

[Anonymous]

Hi,

I have Month column which of text data type having Month in the format of Jan, Feb, Mar, Apr.... I want to convert to format like January, February in Month format. Because I'm sorting, it is sorted alphabetically. I want to sort by Month only. How I can do that?

Thanks

Amit

[Greg_Deckler · Accepted solution]

@Anonymous Yeah, I was afraid of a circular dependency which I why I didn't suggest that approach. You likely need to do that in Power Query Editor with nested if then else statements since Power Query does not have a switch statement. Another way is to duplicate the column and then do a bunch (12) replace steps. 

[V-lianl-msft · Accepted solution]

Please add a new column in the query editor of the transform data.

Then select the month column and click Sort by column.

[jtecco]

I received a column of null. 

[Greg_Deckler]

@Anonymous Well, you will need a numeric "Sort by" column to get the sorting correct. So if you have a date column, just create a column like MONTH([Date]) and then set your Sort by column to that.

[Anonymous]

Ok, so I have to convert Jan to January. I have a table like this-

I want to convert to this

How I can achieve this? Should I manually create a second table?

[PaulDBrown]

if the tabke contains a date column, follow @Greg_Deckler  suggestion. If not, create a new calculated column using:

Mont number = SWITCH(Table [Month],
"Jan", 1,

"Feb, 2,

"Mar", 3,

"Apr", 4,

"May", 5,

"Jun", 6,

"Jul", 7,

"Aug", 8,

"Sep", 9,

"Oct", 10,

"Nov", 11,

12)

set the data type as whole number.

Then select the month column and in the ribbon select "Sort by column" and choose the month number column.

[Anonymous]

When I do this there is an error- 

Failed to save modifications to server. A circular dependency was detected. 

[Greg_Deckler · Accepted solution]

@Anonymous Yeah, I was afraid of a circular dependency which I why I didn't suggest that approach. You likely need to do that in Power Query Editor with nested if then else statements since Power Query does not have a switch statement. Another way is to duplicate the column and then do a bunch (12) replace steps.