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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Invesco
Helper V
Helper V

Convert timestamp to Time in Power Query

Hi Experts

 

I have found the following online.....

 

1. 74928 should read 07:49:28

2. 164054 should read 16:40:54 and so on

 

Custom Column 

= Table.ReplaceValue(#"Source",each [email_sent_time_id],each Time.FromText(Text.From([email_sent_time_id])),Replacer.ReplaceValue,{"email_sent_time_id"} - getting an error syaing column not found.

 

See image of the column i am trying to convert to time...

Invesco_0-1649773266966.png

 

1 ACCEPTED SOLUTION
TheoC
Super User
Super User

Hi @Invesco 

 

If you're not getting the output you want using M, I suggest just using "Split Column by Number of Characters" and then merge.

TheoC_0-1649782540117.png

 

You can then merge like below:

TheoC_1-1649782327970.png

Output will be:

TheoC_2-1649782394519.png

You can then change the column format type to Time:

TheoC_3-1649782466295.png

Hope this helps!

Theo 🙂

 

 

 

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

4 REPLIES 4
tackytechtom
Super User
Super User

Hi @Invesco ,

 

Here I got to a solution:

tomfox_0-1649782256467.png

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczLCQAhDIThXnIWzIx5WYts/22sHhbcU/jJx6wljA50KiFNyCvSJmvfGizlhDxtCazrOIL7848wdTs+wyM/f8/D76AlxvH0oajtnxc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [email_sent_day_date = _t, email_sent_week_start_date = _t, email_sent_time_id = _t, crm_email_send_email_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"email_sent_day_date", type date}, {"email_sent_week_start_date", type date}, {"email_sent_time_id", type text}, {"crm_email_send_email_id", Int64.Type}}),
    #"Pad Value" = Table.ReplaceValue(#"Changed Type",each [email_sent_time_id],each Text.PadStart ([email_sent_time_id], 6, "0"),Replacer.ReplaceValue,{"email_sent_time_id"}),
    #"Replace Value" = Table.ReplaceValue(#"Pad Value",each [email_sent_time_id],each Time.FromText(Text.From([email_sent_time_id])),Replacer.ReplaceValue,{"email_sent_time_id"})
in
    #"Replace Value"

 

I added an additional step called "Pad Value" where I added leading 0 to the string:

tomfox_3-1649782579099.png


Without that step the first value would have been displayed wrongly:

tomfox_2-1649782472943.png

 

Let me know if this works for you or if you get stuck somewhere 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Hi Tom , can you kindly add you M script steps into mine please....

#"Inserted Text After Delimiter" = Table.AddColumn(dev_ema_crm_email_detail_View, "Field Force", each Text.AfterDelimiter([crm_salesforce], " "), type text),
#"Added Custom" = Table.AddColumn(#"Inserted Text After Delimiter", "MCCP Flag", each if[mcp_flag] = 0 then "No" else "Yes"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "PadValue", each Text.PadStart( Text.From([email_sent_time_id]), 6, "0"))
in
#"Added Custom1"


Hi @Invesco ,

 

I'll give it a try 🙂

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

#"Inserted Text After Delimiter" = Table.AddColumn(dev_ema_crm_email_detail_View, "Field Force", each Text.AfterDelimiter([crm_salesforce], " "), type text),
#"Added Custom" = Table.AddColumn(#"Inserted Text After Delimiter", "MCCP Flag", each if[mcp_flag] = 0 then "No" else "Yes"),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"email_sent_day_date", type date}, {"email_sent_week_start_date", type date}, {"email_sent_time_id", type text}, {"crm_email_send_email_id", Int64.Type}}),
#"Pad Value" = Table.ReplaceValue(#"Changed Type",each [email_sent_time_id],each Text.PadStart ([email_sent_time_id], 6, "0"),Replacer.ReplaceValue,{"email_sent_time_id"}),
#"Replace Value" = Table.ReplaceValue(#"Pad Value",each [email_sent_time_id],each Time.FromText(Text.From([email_sent_time_id])),Replacer.ReplaceValue,{"email_sent_time_id"})
in
   #"Replace Value"

 

Does this work? 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

TheoC
Super User
Super User

Hi @Invesco 

 

If you're not getting the output you want using M, I suggest just using "Split Column by Number of Characters" and then merge.

TheoC_0-1649782540117.png

 

You can then merge like below:

TheoC_1-1649782327970.png

Output will be:

TheoC_2-1649782394519.png

You can then change the column format type to Time:

TheoC_3-1649782466295.png

Hope this helps!

Theo 🙂

 

 

 

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.