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.
Hi.
I am new to Power BI. I imported data from an Excel spreadsheet which includes a time column properly formated in Excel as hh:mm. However in Power BI every time slot is shown as 1899-12-31 hh:mm for example 1899-12-31 10:00:00 AM. I merged the time column with the date column but the 1899-12-31 date remains in the new column. Please help?
Solved! Go to Solution.
That is correct.
However, it looks like you are changing the format in the modelling tab of the main pbi desktop window. If you change the column type in the Query Editor from Date/Time to Time, it also solves the problem.
A little bit late to the game(as the original post is from 2016:)) but i also had the same issue. I used the following expression to create a date table:
Hi @Gerhard1957
Does the date: 1899-12-31 appear where there is no date value in the original data? i.e. there is just a time value?
Have you tried changing the field type to "Time"?
Hi @bullius,
>>Does the date: 1899-12-31 appear where there is no date value in the original data? i.e. there is just a time value?
Yes, it as you said. This case also appeared when you change date format to date/time.
Default table:
Changed format:
Date format convert to [date] field + 12:00 AM, time format convert to 1899-12-31 + [Time] field.
You can modify the format to show the correct result:
Date:
time:
Result:
In summary, I think 12:00 AM and 1899-12-31 is the default value of the column which does not contain date and time value.
Regards,
Xiaoxin Sheng
That is correct.
However, it looks like you are changing the format in the modelling tab of the main pbi desktop window. If you change the column type in the Query Editor from Date/Time to Time, it also solves the problem.
Hi,
I'm also new and trying to figure out why I have 13/12/1899 00:00:00 in my report. I work in an automotive industry and try to do a report on how many items we build per hour.
So, in the excel file I upload to PowerQuery there is a date and time in separate columns (it's an automated file) and I modified the setting to date and time (see below).
When I upload the dataset to PowerQuery, I change the setting as well to date and time. I close the Power Query Editor and in the Power PBI app I have a formula:
For this column, I changed the setting in modelling tab to Time format.
It seems to be working for some items ok but there are some that are shown as 31/12/1899 00:00:00.
What I am doing wrong?
Thanks guys
OK. I found a work-around by splitting the time column and then deleting the 1899-12-31 column. However, I would still like to prevent the problem if possible.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |