cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 NamesDepartment CallsHandledAbandonedAbdn %Longest WaitAWTLongest CallAHTTotal ConnectedMax Wait to Abandon
AA CC Emergency RepairRepair1551203522.6%0:08:310:00:510:27:230:02:326:43:290:05:38

 

When I load this data into powerBI desktop it loads like this

Queue NamesDepartmentCallsHandledAbandonedAbdn %Longest WaitAWTLongest CallAHT
AA CC Emergency RepairRepair1551203522.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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Data loading incorrectly, Issue with data type of data.

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.

 

Specializing in Power Query Formula Language (M)

View solution in original post

9 REPLIES 9
Highlighted
Community Champion
Community Champion

Re: Data loading incorrectly, Issue with data type of data.

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.

Specializing in Power Query Formula Language (M)
Highlighted
Frequent Visitor

Re: Data loading incorrectly, Issue with data type of data.

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.

Highlighted
Community Champion
Community Champion

Re: Data loading incorrectly, Issue with data type of data.

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"

 

Specializing in Power Query Formula Language (M)
Highlighted
Microsoft
Microsoft

Re: Data loading incorrectly, Issue with data type of data.

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.

 

1.PNG

 

2.PNG

3.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Champion
Community Champion

Re: Data loading incorrectly, Issue with data type of data.

@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

 

Specializing in Power Query Formula Language (M)
Highlighted
Frequent Visitor

Re: Data loading incorrectly, Issue with data type of data.

@v-yulgu-msft

 

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? 

Highlighted
Community Champion
Community Champion

Re: Data loading incorrectly, Issue with data type of data.

@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).

 

Specializing in Power Query Formula Language (M)
Highlighted
Community Champion
Community Champion

Re: Data loading incorrectly, Issue with data type of data.

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.

 

Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Frequent Visitor

Re: Data loading incorrectly, Issue with data type of data.

@MarcelBeug 

 

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.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors