Forum Discussion
Solved
Power Query List.Dates with today function
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 |
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( (_) ) )Spoilerlet 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"
4 Replies
askspepsi - is the date format in your data 'mm/dd/yyyy' or 'dd/mm/yyyy' ?
ChrisMendoza - the date format is 'dd/mmm/yyyy'
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( (_) ) )Spoilerlet 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"ChrisMendoza - thank you for your valuable time and feedback. The Query is working fine.
