Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Convert numbers to month

Hi everybody,   I have a column with months dates in numbers. I want to convert it to text and keep the monthly order as Jun, Feb, Mar, Apr, etc..   I tried several trick like grouping or enterin...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

    The month column is RS month. I need to convert thos numbers in text as Jan, Feb, Mar, etc. but keeping the monthly order when showed in a graph.

    In this scenario, you can use the formula below to create a new calculate column to convert the numbers in text as Jan, Feb, Mar, etc. :smileyhappy:

    Short Month =
SWITCH (
    Table1[RS Month],
    1, "Jan",
    2, "Feb",
    3, "Mar",
    4, "Apr",
    5, "May",
    6, "Jun",
    7, "Jul",
    8, "Aug",
    9, "Sep",
    10, "Oct",
    11, "Nov",
    12, "Dec",
    BLANK ()
)

    To keep the monthly order for the "Short Month" column, you can use the 'Sort by Column' option under Modeling tab.

    1. Select "Short Month" column.
    2. Click 'Sort by Column' option under Modeling tab.
    3. Select "RS Month" column.

    Regards

spuder's avatar
spuder
Resolver IV
9 years ago

Anonymous

 

Could you show me the column where you'd like to extract the Month short?

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

spuder

The month column is RS month. I need to convert thos numbers in text as Jan, Feb, Mar, etc. but keeping the monthly order when showed in a graph.

 

vanessafvg

can access the page, company firewall block it

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

    The month column is RS month. I need to convert thos numbers in text as Jan, Feb, Mar, etc. but keeping the monthly order when showed in a graph.

    In this scenario, you can use the formula below to create a new calculate column to convert the numbers in text as Jan, Feb, Mar, etc. :smileyhappy:

    Short Month =
SWITCH (
    Table1[RS Month],
    1, "Jan",
    2, "Feb",
    3, "Mar",
    4, "Apr",
    5, "May",
    6, "Jun",
    7, "Jul",
    8, "Aug",
    9, "Sep",
    10, "Oct",
    11, "Nov",
    12, "Dec",
    BLANK ()
)

    To keep the monthly order for the "Short Month" column, you can use the 'Sort by Column' option under Modeling tab.

    1. Select "Short Month" column.
    2. Click 'Sort by Column' option under Modeling tab.
    3. Select "RS Month" column.

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi v-ljerr-msft,

       

      it works perfectly thanks!

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    paste this into a blank power query

     

    let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
    let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
    in
    InsertWeekEnding
    in
    CreateDateTable