Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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
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
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)
)
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 |