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.

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.

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.

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

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

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

Duration import from Excel incorrect | Microsoft Fabric Community

17 Replies

adudani
Memorable Member
3 years ago
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
  Not applicable
  3 years ago
  When I change my type to duration, the entire column errors
  - adudani
    Memorable Member
    3 years ago
    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
Not applicable
3 years ago
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
Memorable Member
3 years ago
do text after delimeter instead of range.
use the following setting
- Anonymous
  Not applicable
  3 years ago
  I think the fact that there are two delimiters it's not working quite right.
Anonymous
Not applicable
3 years ago
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.

sample10201.pbix24 KB
ronrsnfld
Super User
3 years ago
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
  Not applicable
  3 years ago
  HI there. When I open the Advanced Editor this is what I see. Where do I paste this new code?
  - ronrsnfld
    Super User
    3 years ago
    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