Transform Text to Month number query editor

[cristianml]

Hi,

I need to transform the Month name to month number, In same column or if not possible in aditional column. I tried the following custom column but doesn't work.

Month Name Step:

if I change Data type into Date it gives an error:

[sevenhills]

You can do as another column

Table.AddColumn(#"Changed Type", "Month Number", each Date.Month(Date.FromText([MonthName], [Format="MMMM"])), Int64.Type)

Sample:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlSK1YlWcktNKoJzfBOLkjPALMeCoswcqBhEzqs0LxXKyIGIOJamlxaXgJnBqQUlqblJqUVgnn9yST6M7ZdfhpBA4bikJkM5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MonthName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MonthName", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month Number", each Date.Month(Date.FromText([MonthName], [Format="MMMM"])), Int64.Type)
in
    #"Added Custom"

[cristianml]

Hi @sevenhills ,

it doesn't work

[sevenhills · Accepted solution]

Please try my sample in a new query and if that works then you can tweak to your needs in your query.

FYI, I change the column as "Month Name"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlSK1YlWcktNKoJzfBOLkjPALMeCoswcqBhEzqs0LxXKyIGIOJamlxaXgJnBqQUlqblJqUVgnn9yST6M7ZdfhpBA4bikJkM5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month Number", each Date.Month(Date.FromText([Month Name], [Format="MMMM"])), Int64.Type)
in
    #"Added Custom"