Custom formatting disappears when importing Excel

[Anonymous]

Hello everyone 🙂

Hope you can help me with my issue.

I have a column in my excel that has a custom formatting: [h]:mm:ss. This is because I need an hour count. So, if it is a date format, it will never have an hour higher than 23:59:59

The problem is that when I import the excel, the column changes to Date, and this problem happens.

Does anyone know how to solve this please? 

Thank you in advance.

Stay safe,

Alena

[Greg_Deckler]

You can edit the query and change the Change Type step so that it does not convert it to a date, you probably want to have it be a Duration type in Power Query or Text.

You might need this later: https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[Anonymous]

Hello @Greg_Deckler 

Thank you for your reply. If I change the type to duration, it returns error. 

I didn't quite understand what you meant by "You can edit the query and change the Change Type step so " I don't have a "change type" in my query editor. I don't know if that's what you were talking about.

Thank you so much for your help. The link you shared will definitely be very useful in other situations 🙂

[Greg_Deckler]

Hmm, OK, most of the time Power Query adds a Change Type step automatically to just about any query. What happens if you change the type from Date to Text?

What are the underlying values in the Excel? Stripped of formatting, what is the actual data in the cells?