Forum Discussion
Duration import from Excel incorrect
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
HI there. When I open the Advanced Editor this is what I see. Where do I paste this new code?
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
Sorry this isn't quite working. The #"Convert to Number" and #"Changed Step" are both giving me errors.