Forum Discussion

cristianml's avatar
cristianml
Post Prodigy
4 years ago
Solved

Transform Text to Month number query editor

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:

 

 

 

 

 

 

 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • sevenhills's avatar
    sevenhills
    4 years ago

    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"

     

3 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    4 years ago

    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"

     

     

     

     

      • sevenhills's avatar
        sevenhills
        Super User
        4 years ago

        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"