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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors