Power query can't convert type to time

[mikemck]

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.

 

[Ron Coderre]

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:

let
    Source = Web.Page(File.Contents("[COLOR=#ff0000]YourFilePath[/COLOR]\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?

 

[mikemck]

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.

 

[alansidman]

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.

 

[mikemck]

Hi,

Did you open the file in Excel, and then load it to Power Query? If I open the file first in Excel, Power Query handles the Target Time column with no problem.

What I am needing to do is drop this file into a folder and then refresh to load the new data.

 

[alansidman]

True. I opened in Excel and uploaded to PQ. I am unable to offer further help on this issue.