cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

 

nanutum_0-1593671336247.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: How to Change Type Date AD and BE in same row

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

 

View solution in original post

2 REPLIES 2
Highlighted
Community Champion
Community Champion

Re: How to Change Type Date AD and BE in same row

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

 

View solution in original post

Highlighted
Solution Supplier
Solution Supplier

Re: How to Change Type Date AD and BE in same row

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





Feel free to connect with me:
LinkedIn

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors