Transform Date from DD.MM.YYYY into YYYYMMDD as whole number

[JackTheFruit]

Another solution I found: 

= Number.From(Date.ToText(Date.From( dateval ), [Format="yyyyMMdd", Culture="en-EN"]))

 This is for a single datetime value "dateval" but can also be done to entire fields of course. 

[Anonymous]

let
Source = let StandardDate = (dateColumn as text,DateSchema as text) =>
let

MyDelimiter = Text.Range(DateSchema,Text.PositionOfAny(DateSchema,{"/","-",".","\","_"}),1),
DateList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(dateColumn),
SchemaList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(DateSchema),
TableDate = Table.FromColumns({DateList,SchemaList}),
myDay = "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "D" )[Column1]{0}),
myDD = Text.Range(myDay,Text.Length(myDay)-2,2),
MyMonth= "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "M" )[Column1]{0}),
myMM = Text.Range(MyMonth,Text.Length(MyMonth)-2,2),
myYear= "20" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "Y" )[Column1]{0}),
myYYYY = Text.Range(myYear,Text.Length(myYear)-4,4),
DateByParts = myYYYY & "-" & myMM & "-" & myDD,
DateCleaned = Date.FromText(DateByParts)
in DateCleaned
in StandardDate
in
Source

[AndyFL]

Text.From(Date.Year([YourDate]))&
Text.PadStart(Text.From(Date.Month([YourDate])),2,"0")&
Text.PadStart(Text.From(Date.Day([YourDate])),2,"0")

[scottymac]

In Power Query do this:

Date.Year([yourdate])*10000+Date.Month([yourdate])*100+Date.Day([yourdate])

[MTracy]

Honestly, this is simple and brilliant.  Worked perfectly for me with only that one line of code.  Thanks!

[HolgerS]

IS there any function, which trasfers a Date in the format DD.MM.YYYY into a whole number YYYYMMDD?

Thanks.

Holger

[Anonymous · Accepted solution]

Here's how you do it in M (Power Query):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLSMzTQMzIwtFSK1QFyjfUMDYFcAwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateDD.MM.YYYY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateDD.MM.YYYY", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "DateDD.MM.YYYY", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"DateDD.MM.YYYY.1", "DateDD.MM.YYYY.2", "DateDD.MM.YYYY.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DateDD.MM.YYYY.1", Int64.Type}, {"DateDD.MM.YYYY.2", Int64.Type}, {"DateDD.MM.YYYY.3", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"DateDD.MM.YYYY.3", "DateDD.MM.YYYY.2", "DateDD.MM.YYYY.1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"DateDD.MM.YYYY.3", type text}, {"DateDD.MM.YYYY.2", type text}, {"DateDD.MM.YYYY.1", type text}}, "en-GB"),{"DateDD.MM.YYYY.3", "DateDD.MM.YYYY.2", "DateDD.MM.YYYY.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", Int64.Type}})
in
    #"Changed Type2"

Just put this code into Advanced Editor in Power Query and see each step. This is very simple 🙂

Best

Darek

[Anonymous]

Hello Darek,

Thanks for this M query.

In this query I am getting 28 feb  2020 as 2020282, can we change this to 20202802 and same is happening for months less than 10 as well. Please suggest thanks

[HolgerS]

 I did it easier with DateKey = VALUE(FORMAT(Table[Column]; "YYYYMMDD"))

Holger

[Anonymous]

Be careful doing this. This most likely works only because you've got the right regional settings on your computer. If you go to a different setting, the code will break.

The most fool-proof way of doing this is through PQ as I showed you. Moreover, creating calculated columns in DAX should be avoided and used only when there's no other option. Many reasons why.

Best
D.