Forum Discussion

nanutum's avatar
nanutum
Helper 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

     

mahoneypat's avatar
mahoneypat
Microsoft 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