Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
Solved! Go to Solution.
Hi,
The picture should say it all
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!
The option "ADD NEW STEP" seemed to work the best in my case. Thanks for providing this solution.
Hi,
The picture should say it all
Date.FromText(Number.Totext[Date]))
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
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! |
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
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
@MichaelHutchens If you convert it to Text then you could use Text functions to extract like Text.Range.
@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"
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |