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.
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 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
do text after delimeter instead of range.
use the following setting
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.
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
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
