Importing column and converting into date

[ClemFandango]

Hello PowerBI community,

I am in desperate need of help. I have spent about 2 weeks trying to do this and failed miserably.

All I need to do is convert a column into a recognisable date format.

The data is imported as text into a column called Source.Name. The date is shown as 020922.CSV (this is the name of the file imported). I have tried using Delimiter to split .CSV from 020922(uk date), but I get an error inside the column when converting ABC to date. I have also tried splitting 020922 into separate day, month and year columns and changing format to number, and adding leading zeros. This also doesn’t work.

Any idea how I can make a date column out of my Source.Name column?

Any help eternally appreciated,

CF

[BA_Pete · Accepted solution]

Hi @ClemFandango ,

Add a new custom column with the following calculation:

let xDate = Text.BeforeDelimiter([Source.Name], ".") in
Text.Combine(
    {
        "20" & Text.End(xDate, 2),
        Text.Range(xDate, 2, 2),
        Text.Start(xDate, 2)
    },
    "-"
)

This outputs as text in ISO format, allowing you to convert to any type/locale you need afterwards.

Example output:

Pete

[ClemFandango]

Thank you so much. You have no idea how long i previously spent on this. Your suggestion works wonderfully. 

[Smalfly]

Hi @ClemFandango ,

try these steps:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY0NDIy1EsuLlOK1YlWMjA0APIRXCMUrjGqrJGFgRGKrCGcGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",".csv","",Replacer.ReplaceText,{"Source.Name"}),
#"Added Custom Column" = Table.AddColumn(#"Replaced Value", "Custom", each Text.Combine({Text.Middle([Source.Name], 2, 2), "/", Text.Start([Source.Name], 2), "/", Text.Middle([Source.Name], 4)}), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom Column",{{"Custom", type datetime}})
in
#"Changed Type1"