How to Change Type Date AD and BE in same row

[nanutum]

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

[Smauro]

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.

    Cor =

        Table.AddColum(

            Step,

            "Is BE",

            each

                let

                    d = [Date],

                    one = Number.FromText(Text.Start(d, 2)),

                    two = Number.FromText(Text.Start(Text.End(d, 7), 2)),

                    three = Number.FromText(Text.End(d, 4))

                in

                    if three > 2500 then

                        #date(three, two, one)

                    else

                        #date(three, one, two),

            type date

        )

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

[mahoneypat · Accepted 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