Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
66 | |
61 | |
46 | |
45 |