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

     

     

     

    • 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