Convert whole number column ("YYYYmmDD") to date column

[MichaelHutchens]

Hi folks,

I thought this might be simple but I'm having real trouble finding the answer - I'm looking to convert a whole number column, in Power Query, in the YYYYMMDD format to a date column, including year, month, and day. Here's some example data:

Any help would really be appreciated 🙂

[jhutson]

FIRST, change the Data Type to TEXT.

NEXT, change the Data Type to DATE --- it will ask if you want to replace the existing type conversion or add a new step

Choose ADD NEW STEP (Do not Replace current data type that we first converted to text)

This has stumped me for so long...adding custom columns, using text delimiters, etc. BUT THIS METHOD WORKS EVERY TIME!

Hope this helps a lot of people the way it helped me!

[Marvin_Scott]

The option "ADD NEW STEP" seemed to work the best in my case. Thanks for providing this solution.

[Ashish_Mathur · Accepted solution]

Hi,

The picture should say it all

[zz701]

This helped me! Thank you!

[Ashish_Mathur]

You are welcome.

[Syndicate_Admin]

Date.FromText(Number.Totext[Date]))

[Anonymous]

Hi @MichaelHutchens ,

In Power Query>> Transform Tab>>Any Column>>Date type: Date.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Polly

[zz701]

This works in Power Query Online in a Dataflow Gen 1, but not in Power BI Desktop. Very weird. I'm updated to the latest version. Instead, I created a Custom Column using the formula:  Date.From(Text.From([Invoice Date], "en-US"))

Note: that is using Date.From AND Text.From

[CNENFRNL]

[aj1973]

Hi @MichaelHutchens 

In Power Query Split the column into 3 columns then merge them together and change the type to date

You can add a step to remove the split columns

[Greg_Deckler]

@MichaelHutchens If you convert it to Text then you could use Text functions to extract like Text.Range.

[MichaelHutchens]

Hi @Greg_Deckler , I'd like to convert it to a short date format, like the below:

[Greg_Deckler]

@MichaelHutchens Change the column to Text and then it is just Date.From([Column])

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjQwNjRSitWBcoyMTOEcQyNDBMfA1MAEwbEwNENwzAzNkWSQDDAwN7RUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type text}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type1", "Date", each Date.From([Column1]), type date)
in
    #"Inserted Date"

Or, change to Text and then change To Date:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjQwNjRSitWBcoyMTOEcQyNDBMfA1MAEwbEwNENwzAzNkWSQDDAwN7RUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Column1", type date}})
in
    #"Changed Type2"