How to convert weird integer date and time values

[Anonymous]

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!

[donsvensen]

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]

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]

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]

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]

Hi @Anonymous

You may refer to below to add a custom column.

Regards,

Cherie

[Anonymous]

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.