Date in two formats

[shanezer]

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?

[dufoq3 · Accepted solution]

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]

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]

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

[uzuntasgokberk]

Hello @shanezer,

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

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.

target=_blanktarget=_blankView solution in original post