Query Function, return value and not table...

[Gusd8]

Greetings, thank you for looking...

The situation is...I have to calculate SLAs on various rules. I have taken teh approach of having a "Start Time" and then calculating an "Expected Finish" time based on the rules (Could be hours, could be days, could be end of day)

I have to evaluate if the the start datetime is on a weekend, on a holiday, before shift start, after shift ends and if there is sufficient time to complete the task before the end of the shift. 

I created a function that will look at a date...if it is on a weekend...it will push the start date out to the next business day (if end date is a holiday it will push it out 1 more day).

Here is where I need help...when invoking the function it I get my single date, but when I use it in a table as a new column...it creates a table that has to be expended.

Unfortunately when using the function as part of an if then...I am not able to expand the column...so what I need is my function to return a straight value that can be used in the if then.

The function is below...any assistance would be greatly appreciated...

(StartDate as date) =>

let
Source = List.Dates(StartDate,1,#duration(1,0,0,0)),

// Converting list to table
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), // Converting list to table
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Activity Date"}}), // Renaming to Activity Date
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Days to Add", each 1), // Adding Days to Add column with value of 1
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Days to Add", Int64.Type}}), // Changing Days to Add to whole number
#"Added Custom2" = Table.AddColumn(#"Changed Type", "CustomMakeaList", each List.Dates([Activity Date],7,#duration(1,0,0,0))), // List of 7 days from Activity Date
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "WorkingDays", each List.Select([CustomMakeaList],each List.Contains({1..5},Date.DayOfWeek(_)))), // Keeping only Weekdays
#"Added Custom6" = Table.AddColumn(#"Added Custom3", "WorkingDaysNoHolidays", each List.RemoveItems([WorkingDays],HolidayList)), // Removing Holidays
#"Added Custom4" = Table.AddColumn(#"Added Custom6", "NextBusinessDate", each [WorkingDaysNoHolidays]{[Days to Add]-1}), // Selecting Next Working Day
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom4",{{"NextBusinessDate", type datetimezone}}), // Converting to Date/Time/Zone
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "SLA Start", each [NextBusinessDate] + #duration(0,8,0,0)), // Setting SLA Start Time to 8:00 CST
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Activity Date", "Days to Add", "CustomMakeaList", "WorkingDays", "WorkingDaysNoHolidays", "NextBusinessDate"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"SLA Start", type datetime}})
in
#"Changed Type2"

[lbendlin]

Source = List.Dates(StartDate,1,#duration(1,0,0,0)),

Instead you could write {StartDate}. 

Is your weekend Saturday/Sunday? Better specify the FirstDayOfWeek parameter to avoid ambiguity in international setups.

Here's a simplified version that returns the scalar value you requested.

(StartDate)=>
  DateTimeZone.From(
    List.RemoveItems(
      List.Select(
        List.Dates(StartDate, 7, #duration(1, 0, 0, 0)), 
        each List.Contains({1 .. 5}, Date.DayOfWeek(_))
      ), 
      HolidayList
    ){0}
  )
    + #duration(0, 8, 0, 0)