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

JayKC

New Member
Joined
May 14, 2019
Messages
6
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,049
maybe change type to Duration
it will give you 7.16:17:03 (7 days 16 hours 17 minutes 03 seconds)

durdur
184:17:03​
7.16:17:03​
 

JayKC

New Member
Joined
May 14, 2019
Messages
6
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,049
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
 
Last edited:

JayKC

New Member
Joined
May 14, 2019
Messages
6
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

New Member
Joined
May 14, 2019
Messages
6
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,708
Messages
5,446,044
Members
405,378
Latest member
pvergili

This Week's Hot Topics

Top