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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Swalker
Frequent Visitor

in-column replacement "error " using try otherwise and #datetime(YY,MM,DD)

Hi ,

I have a table with Date, FinancialPeriod (= month) and FinancialYear from entries.

The accounting software uses FP and FY as key parameters

Some Dates are wrongly entered : they do not match the FinancialPeriod : example: 

DateFinancialPeriodFinancialYearDay 
28/5/20176201728not okay
3/10/20171120173not okay
31/5/20175201731ok

 

where [Day] is:

 

 

#"Inserted Day" = Table.AddColumn(#"Changed Type1", "Day", each Date.Day([Date]), Int64.Type)

 

 

In order to correct this I create an AltDate comprising of :

 

 

#"Added AltDate" = try Table.AddColumn(#"Inserted Day", "AltDate", (each #datetime([FinancialYear],[FinancialPeriod],[Day],0,0,0)), type datetime)

 

 

However - this works fine for most of the dates but produces <1% errors for the ones listed as below:

DateFinancialPeriodFinancialYearDayAltDateAltDate valid
28/5/2017620172828/6/201728/6/2017
3/10/201711201733/11/20173/11/2017
31/5/20176201731error28/6/2017
31/10/201711201731error28/11/2017
31/1/20172201731error28/2/2017

 

For the AltDate it is important that the AltDate is in the FinancialPeriod and FinancialYear - the day may deviate eg 3 days

Thus if we say that in case of error we substract 3 days from Day : [Day-3] then it should always work eg 31-3=28 (works also in february)

My approach was to use try [expression [Day] ]  otherwise  [expression including Date.AddDays([Day],-3]) like this:

 

 

#"Added Custom" = try Table.AddColumn(#"Inserted Day", "AltDate1", (each #datetime([FinancialYear],[FinancialPeriod],[Day],0,0,0)), type datetime) otherwise Table.AddColumn(#"Inserted Day", "AltDate1", (each #datetime([FinancialYear],[FinancialPeriod],Date.AddDays([Day],-3),0,0,0)), type datetime)

 

 

 

 

but this does not work:

the error in the column (for <1% of all AltDate values) is: 

"Expression.Error: The DateTime operation failed because the resulting value falls outside the range of allowed values."

 

Any help appreciated !

 

2 ACCEPTED SOLUTIONS
HotChilli
Super User
Super User

A few alterations should fix it:

#"step" = Table.AddColumn(#"Added Custom1", "AltDate1", each try #date([FinancialYear], [FinancialPeriod], [Day]) 
otherwise   Date.EndOfMonth(#date([FinancialYear], [FinancialPeriod], 1)))

This will put the end of the month in but you can change it if you want to stick with the (subtract 3 days) method

View solution in original post

Many thanks @HotChilli for this elegant solution !

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

A few alterations should fix it:

#"step" = Table.AddColumn(#"Added Custom1", "AltDate1", each try #date([FinancialYear], [FinancialPeriod], [Day]) 
otherwise   Date.EndOfMonth(#date([FinancialYear], [FinancialPeriod], 1)))

This will put the end of the month in but you can change it if you want to stick with the (subtract 3 days) method

Many thanks @HotChilli for this elegant solution !

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors