Forum Discussion
Change data type in Custom function
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
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
9 Replies
- olgad
Resident Rockstar
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
SourceIt 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
Responsive Resident
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 ConvertedDatetoYou 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.
ronrsnfldSuper User
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]
v-veshwara-msftCommunity Support
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.- 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 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.
