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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors