Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

 

Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    Hi Anonymous 

     

    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 🙂

     

     

     

     

     

4 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    Hi Anonymous 

     

    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 🙂

     

     

     

     

     

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    Hi Anonymous ,

     

    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/

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        tackytechtom
        Most Valuable Professional
        4 years ago

        Hi Anonymous ,

         

        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/