Help needed!! Data modeling: Time

[BPOPE]

Hi

I'm relatively new to BI so still getting to grips with it.  I'm having an issue with time when importing from excel.  My data is the length of time someone has spent on a call.  In excel its format is [h]:mm:ss

When I import the data to BI it's picking it up as Text and is adding a date in first eg 31/12/1899 07:21:37.  If I try to remodel the data to anything other than text I get Error "We can't automatically convert the column to Time type" or what ever type I am trying.

Any help appreciated

Ben

[v-yuta-msft]

Hi BPOPE,

I have made a test but can't reproduce your issue. My steps are like belw:

Create a sample in excel -> Load table to power bi -> Change format of data type.

  --->      --->     --->   

If this issue still exists, could you please provide some dummy data for further analysis?

Regards,

Jimmy Tao  

[BPOPE]

Thanks Jimmy, I did a bit of reformating in Excel and BI is now allowing me to change the data type to Time which is great.  The issue I have now though is no auto sum is appearing on the Time Data so it's only giving me the option for Counts when trying to use it for visuals.  Any ideas on why it would do this?

thanks

Ben

[v-yuta-msft]

Hi BPOPE,

The text type or data time type of value can't be sumed, can only be counted, you should sum values like number type value.

Regards,

Jimmy Tao

[ChrisMendoza]

Hello @BPOPE,

Dealing with Date/Time data types, or lack of, seems to be an ongoing subject matter in the community.

I think firstly, people really need to understand that the formatting that is shown on screen is just that, formatting. That format represents a decimal number.

Once someone can understand that then the problem becomes seeing how the computer interpreted the value/data it was given. Your computer gave you text. Mine gave me Date/Time. An example file would have been nice.

Then you learn how to massage the data into something that is usable. Whether you're using Power Query M or DAX, both have functions that deal with messed up Date/Time.

You might find TIMEVALUE ( DAX ) useful as any date information included in the time_text argument is ignored.