Power Query - How to Power Query not to convert to day time format

[JayKC]

Fairly new to the world of Power Query and BI but loving it. I am sure this has probably been asked and answered before but I have searched quite a bit and tried different things but have not had any success.

I receive a monthly excel file that I receive from a vendor that has Total Hours For Month. I go to clean up the file using Power Query but one thing it does that I don't want is to convert the Total Hours For Month Column

The column shows 184:17:03 (hhh:mm:ss)

When I pull the data into Power Query it converts it to 1/7/1900 4:17:03 PM. I change the type to Time but it just leaves the 4:17:03 PM. When I close and load back to Excel and change the column type in Excel to Custom, I end up with 16:17:03

Would appreciate any direction that can me keep the column with the correct data

Thanks

Jay

 

[sandy666]

maybe change type to Duration
it will give you 7.16:17:03 (7 days 16 hours 17 minutes 03 seconds)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]dur[/td][td][/td][td=bgcolor:#70AD47]dur[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

184:17:03​

[/td][td][/td][td=bgcolor:#E2EFDA]

7.16:17:03​

[/td][/tr]
[/table]

 

[JayKC]

After re-reading this I can see where I may need to clarify.

I have an excel sheet with a Total Hours For Month that contains 184:17:03 using custom format [h]:mm:ss

When I pull the file into Power Query, the Navigate step converts that column to 1/7/1900 4:17:03 Which I am sure is 184/24 to get number of days.

I tried different things nothing worked. Well one thing worked to get it in its original format but it is like six steps. A bit crazy to have to do that much just to get a column back to its original setting.

Is there a function that I can tell Power Query Navigate step not to convert to date format?

Jay

 

[sandy666]

this is only format...
delete change type and you;ll see decimal format (true value of the duration = 7.678506944444444)
you can use format as you wish, eg Duration then...
if you want 184:17:03 after load cleaned data into the sheet simple choose the same format as before: [h]:mm:ss

 

[JayKC]

Hi Sandy

When I try to do that with the 1/7/1900 4:17:03 PM data it gives me an error.

Here is what I did do. You experts out there please don't laugh at me.

I created a new date column with 12/31/1899 0:00:00 date time value.
Created a custom column that subtracted the Converted Date - that New Date. That gives me days.hours:minutes:seconds
I split the columns up so that Days is in one column. Hours in another. Minutes:seconds in a third
New column that takes Days * 24 + Hours.
Then merged that column with minutes:seconds.
Delete all of those temporary columns.

Believe it or not, I get back to the original value.


I sure was hoping there was a much simpler way to keep the data EXACTLY as it is enters into Power Query

 

[JayKC]

Thanks, Sandy

I will give that a try right now

 

[sandy666]

sure, in a meantime try to read here: Calculating Time in Excel

 

[JayKC]

But is it the bottomline that the data gets converted automatically by Power Query and I have to take the steps to convert it back?

 

[sandy666]

you can turn off autodetect here:

 

[JayKC]

Bless you, Sandy!!! Ever in KC, I will treat to some great BBQ!!