Change data type in Custom function

[Ivy_Lau]

Hello,

I am new to Power BI and hope someone could help.

I need to split out period from two dates (named [Datefrom] & [Dateto] below) into a series of dates (named [Date.Date] below).

Custom function as below:

let
Source = (Datefrom as nullable number, Dateto as nullable number) =>
let
Datefrom = if Datefrom is null then Dateto else Datefrom,
Dateto = if Dateto is null then Datefrom else Dateto,
Source = if Datefrom < Dateto
then [Date.Date = {Datefrom .. Dateto}]
else [Date.Date = {Dateto .. Datefrom}]
in Source
in Source

Its work. But problem is everytime I need to change the data type of [Datefrom], [Dateto] into number before invoking and change the data type of [Date.Date] back into date then.

Is there any way to include the data type conversion into the function? Or any simpler way? Many thanks!

Regards,

Ivy

[v-veshwara-msft]

Hi @Ivy_Lau ,

We wanted to follow up on your query to see if you were able to resolve the issue. If so, marking the response as the solution and leaving a kudos would be greatly appreciated, as it helps others in the community facing similar challenges.

If you're still experiencing difficulties or need further clarification, please let us know.

Best Regards,
Vinay.

[v-veshwara-msft]

Hi @Ivy_Lau ,

We just wanted to check in again regarding your issue. If you’ve found a solution, marking the reply as the solution and leaving a kudos would be greatly appreciated—it helps the community and others with similar questions.

If you’re still facing challenges or have further questions, please let us know—we’re here to help and would love to assist you in resolving this.

Looking forward to hearing back from you!

Best regards.

[v-veshwara-msft]

Hi @Ivy_Lau ,
We haven't heard from you since the last response and just wanted to check whether any solution has met your query. If yes, please accept whichever solution has met your query to help others benefit. If no, please feel free to reach out for further assistance.
Thank you.

[ronrsnfld]

Maybe:

(dateFrom as nullable date, optional dateTo as nullable date)=>

let
    duration = Number.Abs(Duration.Days(dateTo-dateFrom)??0),
    d1 = List.Min({dateFrom,dateTo}),
    
    dateList = List.Dates(d1,duration+1,#duration(1,0,0,0))
in 
    [Date.Date=dateList]

[olgad · Accepted solution]

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

[Ivy_Lau]

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

[Chewdata]

Hey,

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.

[Ivy_Lau]

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]

Because the error comes from 2nd step #"Changed Type". Delete this #"Changed Type" step and your function should work.