Forum Discussion

nanutum's avatar
nanutum
Icon for Helper I rankHelper I
6 years ago
Solved

How to Change Type Date AD and BE in same row

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

 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

     

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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

     

  • Smauro's avatar
    Smauro
    Icon for Solution Sage rankSolution Sage
    6 years ago

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