Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Gusd8
Helper I
Helper I

Query Function, return value and not table...

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"

1 REPLY 1
lbendlin
Super User
Super User

 

 

 

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)

 

 

 

 

 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.