Forum Discussion

askspepsi's avatar
askspepsi
Helper II
5 years ago
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 "...
Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    5 years ago

    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"
ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
5 years ago

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