Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
i have many text file to Power Query
in row have
BE = 30/05/2559 D/M/Y and AD Year 5/28/2020 M/D/Y in same row
how to convert all to AD Year M/D/Y
Solved! Go to Solution.
I was not familiar with BE dates, so did a quick Wikipedia search. Please correct my math, but hopefully the approach gives you a way to do this. You can add a Custom Column in the query editor.
= if Date.Year([Date]) > 2100 then #date(Date.Year([Date])-543, Date.Day([Date]), Date.Month([Date])) else #date(Date.Year([Date]), Date.Month([Date]), Date.Day([Date]))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @nanutum You can't really know if a date is in BE or AE just by the date: 1/2/2020 could either be February 1 or January 2.Seeing that your dates are far apart, if you could supply a logic to distinguish them, then you could keep them as text and then do some transformations based on year,e.g.
@mahoneypat you're most probably right. I just found out BE is also Buddhist Era. Good to know.
On a second look, @mahoneypat your solution won't work because it will use the machine's local.
by keeping them as text:
Table.TransformColumns(PreviousStep, {{"Date", each if Number.FromText(Text.End(_, 4)) > 2200 then Date.From(_, "th-TH") else Date.From(_, "en-US"), type date}})
I was not familiar with BE dates, so did a quick Wikipedia search. Please correct my math, but hopefully the approach gives you a way to do this. You can add a Custom Column in the query editor.
= if Date.Year([Date]) > 2100 then #date(Date.Year([Date])-543, Date.Day([Date]), Date.Month([Date])) else #date(Date.Year([Date]), Date.Month([Date]), Date.Day([Date]))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.