Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.
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 |
Solved! Go to 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( (_) ) )
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"
Proud to be a Super User!
@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( (_) ) )
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"
Proud to be a Super User!
@ChrisMendoza - thank you for your valuable time and feedback. The Query is working fine.
@askspepsi - is the date format in your data 'mm/dd/yyyy' or 'dd/mm/yyyy' ?
Proud to be a Super User!