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 and comes in 25/01/2024 but the other comes in 20240125.  The first part I normally change these with is below but I dont know how I do the second and dont want it to put a table in as it multiplies the data too much so only want one column. Code I normally use to change dates DateFromText(Text.End([Date], 4) & "-" & Text.Middle([Date] , 3,2) & "-" & Text.Start ([Date],2)) but I am not sure how to do the If for the 20240125 Any ideas?

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

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    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
  • uzuntasgokberk's avatar
    uzuntasgokberk
    Super User
    2 years ago

    Hello shanezer,

     

    In Power Query side, for example 20240125 data value, you can change type as date or Using Locale(to date)

     

     

     

    Kind Regards,
    Gökberk Uzuntaş

    📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

    🔗 Stay Connected:
    📘 Medium |
    📺 YouTube |
    💼 LinkedIn |
    📷 Instagram |
    🐦 X |
    👽 Reddit |
    🌐 Website |
    🎵 TikTok |

     
  • 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)
      )