Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Transform text column to Date

Hi,

I power Query I have a text column YearMonth and I want to convert this to a date column. I can do this by creating a custom column but how can I do this by Transforming the column as I don't want to add another column and changing the type to date afterwards.
Here is my custom column.

 

= Table.AddColumn(#"Added Items", "Date", each Date.FromText(Text.Range([yearMonth], 0,4) & "-"  & Text.Range([yearMonth], 4,2)))

 

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    Try

        #"Transform to Dates" = Table.TransformColumns(#"Previous Step", {
        {"yearMonth", each 
            let 
                parts = Splitter.SplitTextByLengths({4,2})(_),
                partsN = List.Transform(parts, each Number.From(_))
            in 
                #date(partsN{0},partsN{1},1),
                type date
                }})

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    Try

        #"Transform to Dates" = Table.TransformColumns(#"Previous Step", {
        {"yearMonth", each 
            let 
                parts = Splitter.SplitTextByLengths({4,2})(_),
                partsN = List.Transform(parts, each Number.From(_))
            in 
                #date(partsN{0},partsN{1},1),
                type date
                }})
  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    2 years ago

    Hi Anonymous 

     

    If your YearMonth is of the form 202403 then you can't change the type to date by transforming the column to date type.

     

    Reason being, there's no delimiter to indicate the year and month.  If you had 2024-03 then you could do this.  With the day being assumed to be the 1st.

     

    So you can add a new column and then convert that.  That's not such a big issue is it?

     

    regards

     

    Phil