Convert numbers to month

[Anonymous]

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 entering a new table to edit relationships, however in the graph the months are sorted alphabetically. I also tried the formula Months = text('date'[Month],"MMM") but still doesn't work.

Any help?

Cheers,

Andrea

[DlyGMCT]

Not sure if this will work for you but seems most of the solutions here are overly complex.

try this formula

format(date(1,'date'[Month],1),"MMM")

[vanessafvg]

matt mattson has this blog post

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

use some of the logic here and do it in power query , you might have to convert then convert again.

and then in modelling in power bi set your name column to sort by the number.

[spuder]

Hi @Anonymous

use format instead of text...

Month short = FORMAT('date'[Month],"MMM")

[Anonymous]

Hi @spuder,

thanks for your help. I already tried this but look at the results.. It converts 1 as Dec and all the others as Jan.

[spuder]

@Anonymous

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

[Anonymous]

@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 · Accepted solution]

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. 

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]

Hi @v-ljerr-msft,

it works perfectly thanks!

[vanessafvg]

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