Power Query Convert Date (type date) to serial number

Question

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!

There is something else going on. Either of your methods will convert a "real date" to the correct serial number. Try setting up a table with ONLY those two dates, and try your method again. — Ron Rosenfeld
– Ron Rosenfeld, Commented Feb 2, 2023 at 22:08
Ok, all those methods worked when I created my own simple excel table and loaded into Power Query. It must be the query I'm using that's pulling from Azure Data Explorer. Thanks for the answers and tips. — Joel
– Joel, Commented Feb 2, 2023 at 23:38

horseyride · Accepted Answer · 2023-02-02 20:36:27Z

1

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

edited Feb 2, 2023 at 20:36

answered Feb 2, 2023 at 20:15

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joel Over a year ago

I've tried changing to decimal number, and whole number, but get the same result. n both cases my output is 6.38108E+17 for Feb 1st date, and 6.38107E+17 for Jan 31st date.

Joel Over a year ago

In its original source, it comes in as "2023-02-01 5:00:00 AM +00:00", which I then change to Date type, giving me "2023-02-01"

Collectives™ on Stack Overflow

Power Query Convert Date (type date) to serial number

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related