Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to convert weird integer date and time values

Hello everybody, I hope someone can help me. I am very new to the whole data analysis world, therefore this is maybe an easily solvable problem. I am trying to use power query in Power Bi to conn...
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you for your help! 

 

Apologies for the late response, but I got no notification from the forum.

I am not 100% sure how it works, but it works :) 

 

But how can I convert the time fields? 

 

Thank you in advance.

donsvensen's avatar
donsvensen
Skilled Sharer
7 years ago

Hi

 

When you say time field do you mean the datatype date or ?

 

You can just rightclick the header on the table and choose Change type and pick date

 

or modify the addcolumn step 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjc2tjQwUYrVATFNjAzNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Process_Date = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"Process_Date", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(PreviousStep, "Date", each Date.AddDays(#date(2015,11,18), [Process_Date]-735920), type date)
in
    #"Added Custom"

and specify the datatype as the last argument

 

/Erik

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi

     

    No I mean the "Process_Time" from my source data. And the datatype should be Time, something like 07:15:00 instead of 33213.

     

    Thank you for your help!

    • v-cherch-msft's avatar
      v-cherch-msft
      Microsoft Employee
      7 years ago

      Hi Anonymous

       

      You may refer to below to add a custom column.

       

      Regards,

      Cherie

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi,

         

        Thanks for the answer, but I am not really sure if that works, because 33213 was just a random picked example number. I dont know if the time to this number is 07:15:00. I was never able to convert this time to a proper time, because ordinal conversion in Python is only used for Dates. Sorry for the confusion.