Transform text column to Date

[Anonymous]

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)))

[ronrsnfld · Accepted solution]

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]

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