cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors