How to change month column in text format to date format without getting errors?

[Alchemista]

Hi,

As seen in screenshot below, I have a column with month names in text format. When I try to transform to date it gives me errors all across. I tried to do "parse" and it doesnt pick up on the month names for some reason, still giving errors. What should I do to change to a date/month format so I can rank my sales data by month in reports? 

Also, if Power BI cant tell if June is a month or not, what's the function of "parse"?

Thank you,

[v-piga-msft · Accepted solution]

Hi @Alchemista ,

As alexvc  suggested, you could create the custom column to achieve your requirement.

Please refer to this M query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUTIyMLRQitWJVnJLTSpCF/NNLErOQBZwLCjKzEFVgaLBqzQvFc6PBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Year", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Month] = "January" then 1 else if [Month] = "February" then 2 else if [Month] = "March" then 3 else if [Month] = "April" then 4 else if [Month] = "May" then 5 else if [Month] = "June" then 6 else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Year", type text}, {"Custom", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Year]&"-"&[Custom]&"-"&"1"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom.1", "date"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"date", type date}})
in
    #"Changed Type2"

Here is the output.

You also could refer to my attachement.

Best  Regards,

Cherry

[alexvc]

Hi there,

Date format conversion parting from text can be tricky. A quick fix can be to create a calculated column in your table with an IF(Period="January",1,IF(Period="February",2,.... formula to assign the month number to each record. In the sample data you posted that column should show "6" in all rows, as that's June's month number. Then add a separate column to calculate the date using the formula DATE(day,month,year). If you don't have a day column in your table input 1 and that will give you a date format for the 1st of the month

Let me know if it works

Alejandro