Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
shanezer
Frequent Visitor

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?

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @shanezer, check this:

 

Before

dufoq3_0-1723809536649.png

 

After

dufoq3_1-1723809546268.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

4 REPLIES 4
dufoq3
Super User
Super User

Hi @shanezer, check this:

 

Before

dufoq3_0-1723809536649.png

 

After

dufoq3_1-1723809546268.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

shanezer
Frequent Visitor

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? 

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
Super User
Super User

Hello @shanezer,

 

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

 

uzuntasgokberk_0-1723797330587.png

 

uzuntasgokberk_1-1723797428037.png

 

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 |

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.