Forum Discussion

ClemFandango's avatar
ClemFandango
Advocate II
3 years ago
Solved

Importing column and converting into date

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

Need Help
Show and Tell
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

3 Replies

  • Smalfly's avatar
    Smalfly
    Responsive Resident
    3 years ago

    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" 

     

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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's avatar
      ClemFandango
      Advocate II
      3 years ago

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