Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
25 | |
14 | |
14 | |
12 |