Duration import from Excel incorrect

[Anonymous]

I set my column in Excel to hh:mm:ss, but when it imported into Power BI it is 12/31/1899 12:00:00AM.

What's the magic step to get my minutes and seconds back in my Power BI query editor? I'm a new user so please use small words, please and thank you.

[ronrsnfld]

Excel stores date/times as fractions of a day and starts it's calendar on 1/1/1900. PQ starts on 12/31/*1899.

So I believe if you change the data into a decimal number, subtract one, and then convert it to a duration, things should work:

Excel data

Imported into Power BI in the Transform Data window

Paste code into Advanced Editor

Except for first two lines

let
    Source = Excel.Workbook(File.Contents("C:\Users\ron\OneDrive\Documents\Book1.xlsx"), null, true),
    Duration_Table = Source{[Item="Duration",Kind="Table"]}[Data],

    #"Convert to Number" = Table.TransformColumns(Duration_Table, {"Funnel duration", each Duration.From(Number.From(_)-1)}),

    #"Set Duration type" = Table.TransformColumnTypes(#"Convert to Number",{{"Funnel duration", type duration}})
in
    #"Set Duration type"

Results

[Anonymous]

HI there.  When I open the Advanced Editor this is what I see. Where do I paste this new code?

[ronrsnfld]

Probably after your #"Changed Type" line. 

Don't forget to not paste the first two lines as stated.

Also, you will need to change the Table reference in my #"Convert to Number" step to the appropriate one in your code (probably to #"Changed Step"; and you should probably eliminate the type definition for the Funnel Duration column in your #"Changed Step" code

[Anonymous]

Sorry this isn't quite working. The #"Convert to Number" and #"Changed Step" are both giving me errors.

[ronrsnfld]

I would expect errors because you

 1. Did not paste the code in the proper location (afterr your #"Changed Step" line (step))

 2. and also you didn't change the table reference in my #"Convert to Number" step.

[Anonymous]

My apologizes. I'm new to Power BI and I didn't understand your instructions completely.

[Anonymous]

Hi @Anonymous ,

Please follow the step and make a try.

Ensure that the column is Date/Time type. Select the Funnel duration column and then select "Time Only".

Times are extracted.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[adudani]

do text after delimeter instead of range.
use the following setting

[Anonymous]

I think the fact that there are two delimiters it's not working quite right.

[Anonymous]

Ok I'm getting clsoer, thanks for the patience. But I cannot get the Index and the Range that I want.

I see this 12:00:00 AM and I just want the 00:00. I thought it would be 3 and 5 but that returned 00 AM. Any advice?

[adudani]

hi @Anonymous,

left click on the "ABC123"  

select duration (clock)

Please let me know if this resolves your question.

Appreciate a thumbs up if his is helpful.

[Anonymous]

When I change my type to duration, the entire column errors

[adudani]

@Anonymous  try using the "Time" instead of duration.
If you still get an error/ not the result that is required.

then, use the use "Split Colum"-> "By Delimiter"-> "left-most delimeter" ->use space as the delimiter.
you will get two columns, one with date and one with time along with AM/PM. in the time column, change the type and see if it is resolved.

let me know

[Anonymous]

Ok closer. The time is almost correct. If it says 12:04:54 the number I actually need is 04:54. however I don't get the option to split the column when the type is Time. 

 

[adudani]

add / transform column -> extract (ABC123) -> range -> specify the starting index and charecters to extract.

[Anonymous]

Where do I type that?

[adudani]

select range