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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,075
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,075
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,096,029
Messages
5,447,974
Members
405,475
Latest member
sagenow

This Week's Hot Topics

Top