topic Re: Transform Date from DD.MM.YYYY into YYYYMMDD as whole number in DAX Commands and Tips

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

HolgerS — Sat, 24 Aug 2019 06:17:23 GMT

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

HolgerS — Sat, 24 Aug 2019 06:19:12 GMT

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

Thanks.

Holger

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

Anonymous — Sat, 24 Aug 2019 12:26:38 GMT

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

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

HolgerS — Mon, 26 Aug 2019 12:50:24 GMT

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

Holger

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

Anonymous — Mon, 26 Aug 2019 16:30:42 GMT

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.

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

Anonymous — Fri, 28 Feb 2020 15:41:02 GMT

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

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

scottymac — Wed, 20 Apr 2022 19:13:18 GMT

In Power Query do this:

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

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

AndyFL — Sun, 11 Sep 2022 20:49:50 GMT

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

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

MTracy — Mon, 30 Jan 2023 17:20:57 GMT

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

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

Anonymous — Wed, 01 Mar 2023 10:09:12 GMT

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

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

JackTheFruit — Tue, 04 Jul 2023 12:01:14 GMT

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.