Convert timestamp to Time in Power Query

[Invesco]

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...

[tackytechtom]

Hi @Invesco ,

Here I got to a solution:

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:

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

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

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

[Invesco]

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"

[tackytechtom]

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/

[TheoC · Accepted solution]

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.

You can then merge like below:

Output will be:

You can then change the column format type to Time:

Hope this helps!

Theo 🙂