cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adityavighne
Responsive Resident
Responsive Resident

Convert text number into DateTime

Hi

I have datetime field in my data where datatype of that field is text and value looks like number. I want to convert this in to Date-Time.

How I can do this.

 

value

1630000827000

 

adityavighne_0-1631855669583.png

Thansk & regards,

aditya vighne

1 ACCEPTED SOLUTION

@adityavighne , I added this number to a new power bi table(enter data) and was able to create a new custom column in power query

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQzNgACCyNzIKkUq4MhEgsA", 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}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Column1]/1000))
in
#"Added Custom"

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

3 REPLIES 3
adityavighne
Responsive Resident
Responsive Resident

@amitchandak no this is not working giving an error

Error: We cannot convert the value "1630000827000" to type Function.

@adityavighne , I added this number to a new power bi table(enter data) and was able to create a new custom column in power query

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQzNgACCyNzIKkUq4MhEgsA", 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}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Column1]/1000))
in
#"Added Custom"

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

amitchandak
Super User
Super User

@adityavighne , Try a new column power query

 

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [timestamp]/1000)

 

 

or

 

 

#datetime(1900, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [timestamp]/1000)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.