Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MichaelHutchens
Helper IV
Helper IV

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

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:

MichaelHutchens_0-1630779583366.png


Any help would really be appreciated 🙂

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

The picture should say it all

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
jhutson
New Member

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)

jhutson_0-1680725430369.png

 

jhutson_1-1680725460172.png

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!

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

Ashish_Mathur
Super User
Super User

Hi,

The picture should say it all

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

v-rongtiep-msft
Community Support
Community Support

Hi @MichaelHutchens ,

 

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

111.PNG

 

222.PNG

333.PNG

 

 

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

CNENFRNL
Community Champion
Community Champion

Screenshot 2021-09-04 205242.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

aj1973
Community Champion
Community Champion

Hi @MichaelHutchens 

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

aj1973_0-1630781304121.png

 

You can add a step to remove the split columns

aj1973_1-1630781387342.png

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

MichaelHutchens_0-1630780778194.png

 

@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"

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.