Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
HolgerS
Frequent Visitor

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

 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

10 REPLIES 10
JackTheFruit
New Member

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. 

RicardoTeixeira
Frequent Visitor

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
Frequent Visitor

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

scottymac
Regular Visitor

In Power Query do this:

 

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

 

 

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

HolgerS
Frequent Visitor

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

Thanks.

 

Holger

Anonymous
Not applicable

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
Not applicable

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

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

Holger

Anonymous
Not applicable

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.

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors