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