March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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:
Date | FinancialPeriod | FinancialYear | Day | |
28/5/2017 | 6 | 2017 | 28 | not okay |
3/10/2017 | 11 | 2017 | 3 | not okay |
31/5/2017 | 5 | 2017 | 31 | ok |
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:
Date | FinancialPeriod | FinancialYear | Day | AltDate | AltDate valid |
28/5/2017 | 6 | 2017 | 28 | 28/6/2017 | 28/6/2017 |
3/10/2017 | 11 | 2017 | 3 | 3/11/2017 | 3/11/2017 |
31/5/2017 | 6 | 2017 | 31 | error | 28/6/2017 |
31/10/2017 | 11 | 2017 | 31 | error | 28/11/2017 |
31/1/2017 | 2 | 2017 | 31 | error | 28/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 !
Solved! Go to Solution.
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.