Power Query Convert Date (type date) to serial number-CodePudding

I have a column in power query called "Delivery Date", type date.

I want to convert the date to serial number (the same as in excel when you change type date to "general" format. My date is "yyyy-mm-dd"

example:

"2023-02-01" formatted as "44958"

"2023-01-31" formatted as "44957"

This is the output I would like:

Delivery Date	DateCode
2023-02-01	44958
2023-01-31	44957

I've tried this code:

= Table.TransformColumnTypes(Source,{{"Delivery Date", Number.From}})

as well as duplicating my "Delivery Date" column and changing Type to "Int64"

in both cases my output is 6.38108E 17 for Feb 1st date, and 6.38107E 17 for Jan 31st date.

I have not been able to find a solution anywhere online thus far and have been formatting it once it's loaded into excel (which is an unnecessary step).

Thanks!

CodePudding user response：

How about

let  Source = #table({"Delivery Date"},{{"2023-02-01 5:00:00 AM  00:00"}}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Delivery Date", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Delivery Date", type number}})
in  #"Changed Type1"

Transform to date/time

Transform again to number, without replacing original transformation