How to change date type to YYYYMMDD, need from 1/1/2021 change to 20210101.

[wangjuan303]

I need change a date column to a Whole number  column, whole number column should show YYYYMMDD format, I need do it with M code, I know how to step by step do it, but it too much step, Is here anyone know the easy way to change it. Thank you .

[RandomWalker]

There actually is a simple way to achieve this. In the Model View, select the date field that you would like to format. Then in the Properties pan, you'll find a "Date time format" dropdown menu. Select "Custom" and put "yyyymmdd" in for box below. This shuold gives you the desired YYYYMMDD format.

[iabarraganc]

Another trick. You can use this formula

idDate = Table.AddColumn(RenombraFecha, "ID_DATE", each 10000 * Date.Year([DATE_COLUMN]) + 100 * Date.Month([DATE_COLUMN]) + Date.Day([DATE_COLUMN]), Int64.Type),

[OwenAuger · Accepted solution]

Hi @wangjuan303 

The simplest way I can think of is something like this:

Number.From(Date.ToText([Date],"yyyyMMdd"))

Here is a complete sample query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJUitWJVjLWNzRA8Awt9S2APAMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-NZ"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Date Integer", each Number.From(Date.ToText([Date],"yyyyMMdd")), Int64.Type)
in
    #"Added Custom"

Regards,

Owen