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 connect to data from a sql database. This database is the backend database from
a third party software we are using. In some tables the dates and times are in a weird integer format and I want them displayed as normal dates and times. I was able to achieve that for the dates via pyhton. Here an example: 735920 => 2015-11-18 .
I used phyton 2.6.15, the datetime module and the fromordinal() function. But I wanted to automate this with Power Bi. I attached a image from the values.
Any help is much appreciated.

Thanks!


6 Replies

  • donsvensen's avatar
    donsvensen
    Skilled Sharer
    7 years ago

    Hi

     

    If you know that 735920 equals 2015-11-18

     

    Then 733904 must be 735920-733904 = 2016 days before

     

    Then you could add a column in your query

     

    = Table.AddColumn(PreviousStep, "Date", each Date.AddDays(#date(2015,11,18), [Process_Date]-735920))

     

    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))
in
    #"Added Custom"

    /Erik

    • 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