Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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

General Comment
Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    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's avatar
    V-lianl-msft
    5 years ago

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

    Then select the month column and click Sort by column.

     

7 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

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

    Then select the month column and click Sort by column.

     

    • jtecco's avatar
      jtecco
      New Member
      3 years ago

      I received a column of null. 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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.