Forum Discussion

shanezer's avatar
shanezer
Frequent Visitor
2 years ago
Solved

Date in two formats

Hi, I have folders coming in but for some reason it is reading some a different way. I have changed them to text and tried to put a new column in but doesnt work. One when I change it to text is fine...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi shanezer, check this:

     

    Before

     

    After

    let
    Source = Table.FromList({"23/05/2023", "20240518"}, (x)=> {x}, {"Date"}),
    TransformedDate = Table.TransformColumns(Source, {{"Date", each if Text.Contains(_, "/") then Date.FromText(_, [Format="dd/MM/yyyy"]) else Date.From(_, "en-GB"), type date}})
in
    TransformedDate
shanezer's avatar
shanezer
Frequent Visitor
2 years ago

Hi, thanks for the reply but there are two date formats coming through as its folder with 6 files. One file comes through it defaults to ABC123 and is 23/05/2023 and the others are 20240518 so the formats are different. Changing it just to date throws up errors as they arent the same. I need to change it to text and then do a separate column to fix it with some If's I just am not sure how if that makes more sense? 

  • shanezer's avatar
    shanezer
    Frequent Visitor
    2 years ago

    Since I asked the question I got the answer through trial and error by adding a new column and breaking it down into one at a time so this below fixes it  = try Date.FromText(
    Text.Start([Date1], 4) & "-" &
    Text.Middle([Date1], 4, 2) & "-" &
    Text.End([Date1], 2)
    )
    otherwise Date.FromText(
    Text.End([Date1], 4) & "-" &
    Text.Middle([Date1], 3, 2) & "-" &
    Text.Start([Date1], 2)
    )