Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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.
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
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!
Hi @Anonymous
You may refer to below to add a custom column.
Regards,
Cherie
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |