Forum Discussion
Data loading incorrectly, Issue with data type of data.
Hello All,
I am a beginner in powerBI learning it and exploring it. I am facing one problem while loading data from a excel file to powerBI desktop.
Sample data in from excel (I am working on sample call center data)
| Queue Names | Department | Calls | Handled | Abandoned | Abdn % | Longest Wait | AWT | Longest Call | AHT | Total Connected | Max Wait to Abandon |
| AA CC Emergency Repair | Repair | 155 | 120 | 35 | 22.6% | 0:08:31 | 0:00:51 | 0:27:23 | 0:02:32 | 6:43:29 | 0:05:38 |
When I load this data into powerBI desktop it loads like this
| Queue Names | Department | Calls | Handled | Abandoned | Abdn % | Longest Wait | AWT | Longest Call | AHT |
| AA CC Emergency Repair | Repair | 155 | 120 | 35 | 22.6% | 31-Dec-99 12:08:31 AM | 31-Dec-99 12:08:51 AM | 31-Dec-99 12:27:23 AM | 31-Dec-99 12:02:32 AM |
There is problem while loading the duration and when I convert this to time then it is displaying time only like 12:08:31 AM.
What to do now? Guide me
Thank you for your time and help.
Actually, there are some strange things going on with durations in Excel and Power Query.
I created the following video; you may want to skip to 2:37 when the Excel file is imported in Power BI Desktop.
9 Replies
If you go into the Query Editor and check the steps of the query that imports the table, you will see a step "Changed Type".
You can adjust this code by adjusting datetime to date or to duration.
Alternatively, if the "Changed Type" step is the last step in your query, you can change the column types by selecting the columns and change the type with the menu options.
Hi MarcelBeug,
I changed data type from dataTime to time it is showing 12:08:51AM only and when I change it to duration it is showing
error DataFormat.Error: We couldn't convert to Duration.
Details:
31-Dec-99 12:08:31 AMLike this.
Probably you added an additional step. If the value is already converted to datetime, then you can't convert it to duration anymore.
If you change the type of the original value, it should work fine.
In the code below, the type of the first duration field is changed in 2 steps, resulting in the error you indicated; the second duration field is changed to duration directly and this works fine.
let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration1", type datetime}, {"Duration2", type duration}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Duration1", type duration}}) in #"Changed Type1"
Hi J_Tanna,
The error is caused by you were attempting to change a date/time value to duration.
As a workaround, you can first change the data type to text. Then, split the date/time column in order to extract time part. Then, change the new column which only contains time value to duration.
Best regards,
Yuliana Guv-yulgu-msft I would still prefer to change the type to duration before it is changed to datetime.
If you want to convert a datetime value to a duration (because it was a duration in Excel and it got converted to datetime when importing in Power Query), you can subtract #datetime(1899,12,30,0,0,0) (which is equivalent with datevalue 0) and the result will be a duration, also if the duration is more than 24 hours, in which case your workaround will only return the duration modulo 24 hours.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}}), DateTimeToDuration = Table.TransformColumns(#"Changed Type",{{"DateTime", each _ - #datetime(1899,12,30,0,0,0), type duration}}) in DateTimeToDurationWhen I followed the steps mentioned by you then I am able to convert it to duration it is converting to 0.12:08:31 not the 0.0:08:31.
1 step is checked now what to do?
J_Tanna, v-yulgu-msft I'm rather amazed about the direction this topic is going.
Why not adjust the data type directly to duration (before it is converted to datetime!!) as I explained earlier?
Otherwise if you still want to convert a datetime value to duration, the correct way to go is to subtract the zero-value for datetime from the actual datetime value, not via text conversion.
And here I made a mistake in my previous post:
somehow, Power Query seems to use the Excel zero-value #datetime(1899,12,31,0,0,0) instead of the Power Query zero value #datetime(1899,12,30,0,0,0), so a small correction to my formula in my previous post:
Please subtract #datetime(1899,12,31,0,0,0) instead of #datetime(1899,12,30,0,0,0).
