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 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.
Solved! Go to Solution.
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.
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 Gu
When 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).
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.
Your solution worked for me. It has taken some time to understand your solution as I am a powerBI beginner but when I understand it, it worked properly for me.
Thank you so much. Your contribution to powerBI community is amazing. Looking forward to contribute like you and make this community better. Thank you once again.
@v-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 DateTimeToDuration
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 AM
Like 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"
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 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |