Results 1 to 4 of 4

Thread: Power query can't convert type to time

  1. #1
    Seeker mikemck's Avatar
    Join Date
    Jan 2019
    Posts
    7
    Articles
    0
    Excel Version
    2016

    Power query can't convert type to time



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a report that is apparently html formatted, although the extension is .xls .

    Opening the file in Excel is fine, although I get a warning that the file format and extension don't match.

    However, when I try to create a query by loading this file from a folder, as html, I can't get the "target time" column to show as time.

    I am able to get the two date columns to show as dates by changing the type to text and then to type date, but not the target time column.

    Any help or advice is greatly appreciated. I have a attached the file with this post.
    Attached Files Attached Files

  2. #2
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    84
    Articles
    0
    Excel Version
    2013, 2016, O365
    The "time" component has leading zeros followed by
    hour...... 00
    minute.. 00
    second.. 00.00

    This code explicitly parses the value into hh:mm:ss format so it can be coerced into a time value:
    Code:
    let
        Source = Web.Page(File.Contents("YourFilePath\ReactiveWorkOrders-03-14-2019-50789.xls")),
        Data0 = Source{0}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
        #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each 
            Number.ToText(Number.From(Text.Range([Target Time],8,6)),"00:00:00")),
        #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type time}})
    in
        #"Changed Type"
    Does that help?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  3. #3
    Seeker mikemck's Avatar
    Join Date
    Jan 2019
    Posts
    7
    Articles
    0
    Excel Version
    2016
    Hi Ron,

    Dang, I'm still getting errors tyring to change the type to time. This seems crazy to me that I can open this file in Excel and the time shows correctly, but trying to power query this is giving me fits.

    For the SLA resp and Target Date columns changing the type to text and then adding a new step with type as date works to show the dates.

  4. #4
    Acolyte alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    93
    Articles
    0
    Excel Version
    2019
    Cannot replicate your issue. I have downloaded your file. Got the error message you got, but opened anyway. Was able to convert time. Attached is file with Query.
    Attached Files Attached Files

Posting Permissions

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