Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Power Query - How to Power Query not to convert to day time format
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,035
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query - How to Power Query not to convert to day time format

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

    dur dur
    184:17:03
    7.16:17:03
    I know you know but I forgot my Crystal Ball and don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query - How to Power Query not to convert to day time format

    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

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,035
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query - How to Power Query not to convert to day time format

    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 by sandy666; May 14th, 2019 at 06:19 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query - How to Power Query not to convert to day time format

    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

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query - How to Power Query not to convert to day time format

    Thanks, Sandy

    I will give that a try right now

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,035
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query - How to Power Query not to convert to day time format

    sure, in a meantime try to read here: Calculating Time in Excel
    I know you know but I forgot my Crystal Ball and don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query - How to Power Query not to convert to day time format

    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?

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,035
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query - How to Power Query not to convert to day time format

    you can turn off autodetect here:

    I know you know but I forgot my Crystal Ball and don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #10
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query - How to Power Query not to convert to day time format

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •