Forum Discussion
Change data type in Custom function
let
Source = (Datefrom as nullable date, Dateto as nullable date) =>
let
ConvertedDatefrom = try Date.From(Datefrom) otherwise null,
ConvertedDateto = try Date.From(Dateto) otherwise null,
ResolvedDatefrom = if ConvertedDatefrom is null then ConvertedDateto else ConvertedDatefrom,
ResolvedDateto = if ConvertedDateto is null then ConvertedDatefrom else ConvertedDateto,
Dates = if ResolvedDatefrom <= ResolvedDateto
then List.Dates(ResolvedDatefrom, Duration.Days(Duration.From(ResolvedDateto - ResolvedDatefrom)) + 1, #duration(1, 0, 0, 0))
else List.Dates(ResolvedDateto, Duration.Days(Duration.From(ResolvedDatefrom - ResolvedDateto)) + 1, #duration(1, 0, 0, 0)),
Output = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date.Date"})
in
Output
in
Source
It solved my problem, thank you!
But I just wonder why "try" "otherwise" can handle error in case one below but can't in case two.
Just modified the begining :
Case 1 :
Case 2 : all the same except changed the data type to date in advance
Responsive ResidentHey,
The reason for the error is that the error is already raised at the input. It askes for a date type input:
Source = (Datefrom as nullable date,
if you change this to Dateform as any:
(Datefrom as any, Dateto as any) =>
It will handle every type of value.
Unrelated tip for the following line:
ResolvedDateto = if ConvertedDateto is null then ConvertedDatefrom else ConvertedDateto
You can shorten this line using the coalesce function in Power Query using the ?? operator:
ResolvedDateto = ConvertedDatefrom ?? ConvertedDateto ?? null
The ?? checks if the first value is null. if so it uses the second value. If that is also null it gives back null.
but I should already changed the data type of the parameters to any (didn't specify the data type in it).
The only difference between two cases should be:
In case one -
Error occurs when feeding "testing" into Date.From() within the custom function."try" "otherwise" can handle this case & return null as result.
In case two -
Error occurs when changing data type to date by Table.TransformColumnTypes () before invoking the custom function.
"try" "otherwise" can't handle & return error.
If the reason is that the error is already raised at the input for case two.I think below should also return error while it return null.
- dufoq3
Community Champion
Because the error comes from 2nd step #"Changed Type". Delete this #"Changed Type" step and your function should work.
