Power Query List.Dates with today function

[askspepsi]

Greeting all, 

I have table with leave start date, end date and return date like the below table

so using this query to get Leave days from start to end date for those who are having return date

"List.Dates([LeaveFromDate], Number.From([LeaveToDate]-[LeaveFromDate])+1, #duration(1, 0, 0, 0))"

but how to i get Leave days for those employee who return date is having null till the date the return.

Please help me in this case.

Table:-

Id	Leave start date	Leave end date	Leave return date
1	01/05/2021	10/05/2021	11/05/2021
2	01/05/2021	07/05/2021	null

Desired Output:-

Id	Leave start date	Leave end date	Leavedays
1	01/05/2021	10/05/2021	01/05/2021
1	01/05/2021	10/05/2021	02/05/2021
1	01/05/2021	10/05/2021	03/05/2021
1	01/05/2021	10/05/2021	04/05/2021
1	01/05/2021	10/05/2021	05/05/2021
1	01/05/2021	10/05/2021	06/05/2021
1	01/05/2021	10/05/2021	07/05/2021
1	01/05/2021	10/05/2021	08/05/2021
1	01/05/2021	10/05/2021	09/05/2021
1	01/05/2021	10/05/2021	10/05/2021
2	01/05/2021	07/05/2021	01/05/2021
2	01/05/2021	07/05/2021	02/05/2021
2	01/05/2021	07/05/2021	03/05/2021
2	01/05/2021	07/05/2021	04/05/2021
2	01/05/2021	07/05/2021	05/05/2021
2	01/05/2021	07/05/2021	06/05/2021
2	01/05/2021	07/05/2021	07/05/2021
2	01/05/2021	07/05/2021	08/05/2021
2	01/05/2021	07/05/2021	09/05/2021
2	01/05/2021	07/05/2021	10/05/2021
			till today date

[ChrisMendoza · Accepted solution]

@askspepsi - Try this out, see attached:

if [Leave return date] = null then List.Transform( { Number.From ( [Leave start date] )..Number.From ( Date.From ( DateTimeZone.LocalNow () ) ) }, each Date.From( (_) ) ) else List.Transform( { Number.From ( [Leave start date] ) ..Number.From ( [Leave return date] ) }, each Date.From( (_) ) )

Spoiler

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1Dcw1TcyMAJxDA2QOQiZWJ1oJSN0xQbmSByl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Leave start date" = _t, #"Leave end date" = _t, #"Leave return date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Leave start date", type date}, {"Leave end date", type date}, {"Leave return date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Leave return date] = null then List.Transform( { Number.From ( [Leave start date] )..Number.From ( Date.From ( DateTimeZone.LocalNow () ) ) }, each Date.From( (_) ) ) else List.Transform( { Number.From ( [Leave start date] ) ..Number.From ( [Leave return date] ) }, each Date.From( (_) ) )),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

[askspepsi]

@ChrisMendoza  - thank you for your valuable time and feedback. The Query is working fine. 

[ChrisMendoza]

@askspepsi - is the date format in your data 'mm/dd/yyyy'  or 'dd/mm/yyyy' ?

[askspepsi]

@ChrisMendoza  - the date format is 'dd/mmm/yyyy'