Convert table from number to date

[Anonymous]

Hello! I'm trying to convert this table into a date:

It has values as dd/mm/yy, but its not formatted propperly, how can I change them to make them look like 1/02/22, for example?

[tackytechtom]

Hi @Anonymous ,

I'd probably try to fix it in Power Query:

Add a new custom column and use the following code:

= Table.AddColumn(#"Changed Type", "Custom", each Text.Insert ( Text.Insert ( Text.From ([Date]), Text.Length([Date]) - 2, "/"), Text.Length([Date]) - 4, "/"))

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMjJSitWJVjKyNDAxtIQwDQwNjQzBTGMDYyMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Insert ( Text.Insert ( Text.From ([Date]), Text.Length([Date]) - 2, "/"), Text.Length([Date]) - 4, "/"))
in
    #"Added Custom"

Note, the column needs to be in text format for the code to function. Also, the code will only work if the data is either in the format DDMMYY or DMMYY. If your data happens to be DDMYY or DMYY, it won't work.

Let me know, if this helped to solve your issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Hello! Trying to do it as your first method shows me this:

And if I expand it for "Custom" It shows me an error

[tackytechtom]

Hi @Anonymous ,

Interesting... Does it work this way? 🙂

Code

Text.Insert ( Text.Insert ( Text.From ([Date]), Text.Length([Date]) - 2, "/"), Text.Length([Date]) - 4, "/")

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[serpiva64 · Accepted solution]

Hi,

you can create a custom column

(if Text.Length([Column1]) = 5 then Text.Start([Column1],1)&"/"&Text.Middle([Column1],1,2) else Text.Start([Column1],2)&"/"&Text.Middle([Column1],2,2))&"/"&Text.End([Column1],2)

and then change type to Date

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[Anonymous]

Thanks! It worked just fine!