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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

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