Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nanutum
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
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
Smauro
Solution Sage
Solution Sage

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

mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors