IsHoliday for different regions or states

Power Query
1

Hi everyone,
This is my first post into the forum!
If I have multiple states/regions and each state/region had their own public holidays as well as sharing the same National holidays. Is there a way to incorporate this into the Date Table?

For example, do I need to create a column for each state/region indicating whether the ‘Date’ is a holiday (“1” or “0”)? How would I go about resolving this (create a separate states holiday table & then append/merge it with the Date Table?).

Being able to identify if an employee had holidays in a particular month would assist me in calculating how many days they are available in that month.

Any help/advice would be fantastic thank you!

eDNA_Calculate IsHoliday for different states.pbix (193.5 KB)

holiday table

2

Hi @Kazza,

The most simple solution would be to add an IsHoliday column to your Employee table instead.

Table.AddColumn( #"Changed Type1", "IsHoliday", each if Table.IsEmpty( Table.SelectRows( BufferedTable, (BT)=> BT[STATE_ACRONYM] = _[State] and BT[DATE] = _[Work Date])) then false else true

.
But you could amend the Date table Power Query function as well and add a column for each zone.

InsertIsHoliday1 = Table.AddColumn(InsertIsHoliday, "IsHoliday ACT", each not Table.IsEmpty( Table.SelectRows( BufferedTable, (BT)=> BT[STATE_ACRONYM] = "ACT" and BT[DATE] = _[Date])), type logical ),
InsertIsHoliday2 = Table.AddColumn(InsertIsHoliday1, "IsHoliday NSW", each not Table.IsEmpty( Table.SelectRows( BufferedTable, (BT)=> BT[STATE_ACRONYM] = "NSW" and BT[DATE] = _[Date])), type logical ),
InsertIsHoliday3 = Table.AddColumn(InsertIsHoliday2, "IsHoliday NT", each not Table.IsEmpty( Table.SelectRows( BufferedTable, (BT)=> BT[STATE_ACRONYM] = "NT" and BT[DATE] = _[Date])), type logical ),
InsertIsHoliday4 = Table.AddColumn(InsertIsHoliday3, "IsHoliday QLD", each not Table.IsEmpty( Table.SelectRows( BufferedTable, (BT)=> BT[STATE_ACRONYM] = "QLD" and BT[DATE] = _[Date])), type logical ),
InsertIsHoliday5 = Table.AddColumn(InsertIsHoliday4, "IsHoliday SA", each not Table.IsEmpty( Table.SelectRows( BufferedTable, (BT)=> BT[STATE_ACRONYM] = "SA" and BT[DATE] = _[Date])), type logical ),
InsertIsHoliday6 = Table.AddColumn(InsertIsHoliday5, "IsHoliday TAS", each not Table.IsEmpty( Table.SelectRows( BufferedTable, (BT)=> BT[STATE_ACRONYM] = "TAS" and BT[DATE] = _[Date])), type logical ),
InsertIsHoliday7 = Table.AddColumn(InsertIsHoliday6, "IsHoliday VIC", each not Table.IsEmpty( Table.SelectRows( BufferedTable, (BT)=> BT[STATE_ACRONYM] = "VIC" and BT[DATE] = _[Date])), type logical ),
InsertIsHoliday8 = Table.AddColumn(InsertIsHoliday7, "IsHoliday WA", each not Table.IsEmpty( Table.SelectRows( BufferedTable, (BT)=> BT[STATE_ACRONYM] = "WA" and BT[DATE] = _[Date])), type logical ),

.
Or a column containing a delimited list with State Codes for each Date that is a holiday

InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHolidayInRegion", each Text.Combine(List.Transform(Table.SelectRows( BufferedTable, (BT)=> BT[DATE] = _[Date])[STATE_ACRONYM], Text.From), ", "), type text )

.
Here’s my sample file. I hope this is helpful.
eDNA_Calculate IsHoliday for different states.pbix (276.6 KB)

3 Likes
3

Thank you so much for your prompt response! I will look at what you’ve suggested.

4

The solution you have provided me is perfect thanks!

Just one last additional question on this please … if I wanted to create further individual fields for each state to indicate the name of the respective holiday name, such as “IsHolidayName ACT” how would I go about incorporating that into the Date Table?

5

Hi @Kazza,

image
image1488×51 9.1 KB

  1. In green the basic logic remains the same, so we start by filtering down the BufferedTable (=Holiday table) to contain matching dates for the selected state only
  2. In yellow Step 1 returns a table, that will contain none OR one row. So to extract the name we first select that record {0} and then the fieldname [HOLIDAY_TITLE]
  3. In purple Step 2 will result in an error if the table is empty so we add a try - otherwise clause to return a null / blank instead.

Just some food for thought. Now you’ve incorporated the Holiday Name you could loose the boolean filter IsHoliday columns because all non-holidays will be equal to null in the Holiday Name columns…

I hope this is helpful. Updated file attached.
eDNA_Calculate IsHoliday Name for different states.pbix (290.9 KB)

1 Like
6

Brilliant! Thank you so much for the explanation, solution and sample file! I was so close, being new to Power BI, your breakdown of a line of code helps me to understand the syntax and what I was missing.

7

:+1:

Glad I could help

2 Likes
8

Hello @Kazza and @Melissa,

From the "readability"perspective I found very easy to use https://powerqueryformatter.com/

This is how the function from the file looks when formatted:

(StartDate as date, EndDate as date, optional FYStartMonth as number) as table => 
  let
    FYStartMonth           = try if List.Contains({1..12}, FYStartMonth) then FYStartMonth else 1 otherwise 1,
    CurrentDate            = DateTime.Date(DateTime.FixedLocalNow()),
    DayCount               = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
    Source                 = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
    TableFromList          = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType            = Table.TransformColumnTypes(TableFromList, {{"Column1", type date}}),
    RenamedColumns         = Table.RenameColumns(ChangedType, {{"Column1", "Date"}}),
    InsertYear             = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),
    InsertQuarter          = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
    InsertMonth            = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type number),
    InsertDay              = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
    InsertDayInt           = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
    InsertMonthName        = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth    = Table.AddColumn(
      InsertMonthName, 
      "Month & Year", 
      each (try (Text.Range([Month Name], 0, 3)) otherwise [Month Name]) & " " & Number.ToText([Year]), 
      type text
    ),
    InsertCalendarQtr      = Table.AddColumn(
      InsertCalendarMonth, 
      "Quarter & Year", 
      each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), 
      type text
    ),
    InsertDayWeek          = Table.AddColumn(InsertCalendarQtr, "DayOfWeek", each Date.DayOfWeek([Date]), Int64.Type),
    InsertDayName          = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding       = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date], Day.Monday), type date),
    InsertMonthEnding      = Table.AddColumn(InsertWeekEnding, "MonthEnding", each Date.EndOfMonth([Date]), type date),
    InsertWeekNumber       = Table.AddColumn(
      InsertMonthEnding, 
      "ISO Weeknumber", 
      each if Number.RoundDown((Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7) = 0 then Number.RoundDown(
        (Date.DayOfYear(#date(Date.Year([Date]) - 1, 12, 31)) - (Date.DayOfWeek(#date(Date.Year([Date]) - 1, 12, 31), Day.Monday) + 1) + 10) / 7
      ) else 
        if (Number.RoundDown(
          (Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7
        )
          = 53 and (Date.DayOfWeek(#date(Date.Year([Date]), 12, 31), Day.Monday) + 1 < 4))
        then 1
        else Number.RoundDown((Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7), 
      type number
    ),
    InsertISOyear          = Table.AddColumn(
      InsertWeekNumber, 
      "ISO Year", 
      each Date.Year(Date.AddDays(Date.StartOfWeek([Date], Day.Monday), 3)), 
      Int64.Type
    ),
    BufferTable            = Table.Buffer(Table.Distinct(InsertISOyear[[ISO Year], [DateInt]])),
    InsertISOday           = Table.AddColumn(
      InsertISOyear, 
      "ISO Day of Year", 
      (OT) => Table.RowCount(Table.SelectRows(BufferTable, (IT) => IT[DateInt] <= OT[DateInt] and IT[ISO Year] = OT[ISO Year])), 
      Int64.Type
    ),
    InsertCalendarWk       = Table.AddColumn(
      InsertISOday, 
      "Week & Year", 
      each if [ISO Weeknumber] < 10 then Text.From([ISO Year]) & "-0" & Text.From([ISO Weeknumber]) else Text.From([ISO Year]) & "-" & Text.From(
        [ISO Weeknumber]
      ), 
      type text
    ),
    InsertWeeknYear        = Table.AddColumn(InsertCalendarWk, "WeeknYear", each [ISO Year] * 10000 + [ISO Weeknumber] * 100, Int64.Type),
    InsertMonthnYear       = Table.AddColumn(InsertWeeknYear, "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
    InsertQuarternYear     = Table.AddColumn(InsertMonthnYear, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
    AddFY                  = Table.AddColumn(
      InsertQuarternYear, 
      "Fiscal Year", 
      each "FY" & (if FYStartMonth = 1 then Text.End(Text.From([Year]), 2) else if [MonthOfYear] >= FYStartMonth then Text.From(
        Number.From(Text.End(Text.From([Year]), 2)) + 1
      ) else Text.End(
        Text.From([Year]), 
        2
      )), 
      type text
    ),
    AddFQ                  = Table.AddColumn(
      AddFY, 
      "Fiscal Quarter", 
      each "FQ" & Text.From(Number.RoundUp(Date.Month(Date.AddMonths([Date], - (FYStartMonth - 1))) / 3)), 
      type text
    ),
    AddFM                  = Table.AddColumn(
      AddFQ, 
      "Fiscal Period", 
      each if [MonthOfYear] >= FYStartMonth then [MonthOfYear] - (FYStartMonth - 1) else [MonthOfYear] + (12 - FYStartMonth + 1), 
      type text
    ),
    InsertIsAfterToday     = Table.AddColumn(AddFM, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
    InsertIsWorkingDay     = Table.AddColumn(
      InsertIsAfterToday, 
      "IsWorkingDay", 
      each if [DayOfWeek] = 5 then false else if [DayOfWeek] = 6 then false else true, 
      type logical
    ),
    BufferedTable          = Table.Buffer(#"Holiday Table"),
    InsertIsHoliday        = Table.AddColumn(
      InsertIsWorkingDay, 
      "IsHolidayInRegion", 
      each Text.Combine(List.Transform(Table.SelectRows(BufferedTable, (BT) => BT[DATE] = _[Date])[STATE_ACRONYM], Text.From), ", "), 
      type text
    ),
    InsertIsHoliday1       = Table.AddColumn(
      InsertIsHoliday, 
      "IsHoliday ACT", 
      each not Table.IsEmpty(Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "ACT" and BT[DATE] = _[Date])), 
      type logical
    ),
    InsertIsHoliday2       = Table.AddColumn(
      InsertIsHoliday1, 
      "IsHoliday NSW", 
      each not Table.IsEmpty(Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "NSW" and BT[DATE] = _[Date])), 
      type logical
    ),
    InsertIsHoliday3       = Table.AddColumn(
      InsertIsHoliday2, 
      "IsHoliday NT", 
      each not Table.IsEmpty(Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "NT" and BT[DATE] = _[Date])), 
      type logical
    ),
    InsertIsHoliday4       = Table.AddColumn(
      InsertIsHoliday3, 
      "IsHoliday QLD", 
      each not Table.IsEmpty(Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "QLD" and BT[DATE] = _[Date])), 
      type logical
    ),
    InsertIsHoliday5       = Table.AddColumn(
      InsertIsHoliday4, 
      "IsHoliday SA", 
      each not Table.IsEmpty(Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "SA" and BT[DATE] = _[Date])), 
      type logical
    ),
    InsertIsHoliday6       = Table.AddColumn(
      InsertIsHoliday5, 
      "IsHoliday TAS", 
      each not Table.IsEmpty(Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "TAS" and BT[DATE] = _[Date])), 
      type logical
    ),
    InsertIsHoliday7       = Table.AddColumn(
      InsertIsHoliday6, 
      "IsHoliday VIC", 
      each not Table.IsEmpty(Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "VIC" and BT[DATE] = _[Date])), 
      type logical
    ),
    InsertIsHoliday8       = Table.AddColumn(
      InsertIsHoliday7, 
      "IsHoliday WA", 
      each not Table.IsEmpty(Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "WA" and BT[DATE] = _[Date])), 
      type logical
    ),
    InsertHolidayName1     = Table.AddColumn(
      InsertIsHoliday8, 
      "Holiday Name ACT", 
      each try Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "ACT" and BT[DATE] = _[Date]){0}[HOLIDAY_TITLE] otherwise null, 
      type text
    ),
    InsertHolidayName2     = Table.AddColumn(
      InsertHolidayName1, 
      "Holiday Name NSW", 
      each try Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "NSW" and BT[DATE] = _[Date]){0}[HOLIDAY_TITLE] otherwise null, 
      type text
    ),
    InsertHolidayName3     = Table.AddColumn(
      InsertHolidayName2, 
      "Holiday Name NT", 
      each try Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "NT" and BT[DATE] = _[Date]){0}[HOLIDAY_TITLE] otherwise null, 
      type text
    ),
    InsertHolidayName4     = Table.AddColumn(
      InsertHolidayName3, 
      "Holiday Name QLD", 
      each try Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "QLD" and BT[DATE] = _[Date]){0}[HOLIDAY_TITLE] otherwise null, 
      type text
    ),
    InsertHolidayName5     = Table.AddColumn(
      InsertHolidayName4, 
      "Holiday Name SA", 
      each try Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "SA" and BT[DATE] = _[Date]){0}[HOLIDAY_TITLE] otherwise null, 
      type text
    ),
    InsertHolidayName6     = Table.AddColumn(
      InsertHolidayName5, 
      "Holiday Name TAS", 
      each try Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "TAS" and BT[DATE] = _[Date]){0}[HOLIDAY_TITLE] otherwise null, 
      type text
    ),
    InsertHolidayName7     = Table.AddColumn(
      InsertHolidayName6, 
      "Holiday Name VIC", 
      each try Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "VIC" and BT[DATE] = _[Date]){0}[HOLIDAY_TITLE] otherwise null, 
      type text
    ),
    InsertHolidayName8     = Table.AddColumn(
      InsertHolidayName7, 
      "Holiday Name WA", 
      each try Table.SelectRows(BufferedTable, (BT) => BT[STATE_ACRONYM] = "WA" and BT[DATE] = _[Date]){0}[HOLIDAY_TITLE] otherwise null, 
      type text
    ),
    InsertWeekOffset       = Table.AddColumn(
      InsertHolidayName8, 
      "WeekOffset", 
      each (Number.From(Date.StartOfWeek([Date], Day.Monday)) - Number.From(Date.StartOfWeek(CurrentDate, Day.Monday))) / 7, 
      type number
    ),
    InsertMonthOffset      = Table.AddColumn(
      InsertWeekOffset, 
      "MonthOffset", 
      each ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) + Date.Month(Date.From(CurrentDate))), 
      type number
    ),
    InsertQuarterOffset    = Table.AddColumn(
      InsertMonthOffset, 
      "QuarterOffset", 
      each ((4 * Date.Year([Date])) + Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) + Date.QuarterOfYear(
        Date.From(CurrentDate)
      )), 
      type number
    ),
    InsertYearOffset       = Table.AddColumn(
      InsertQuarterOffset, 
      "YearOffset", 
      each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), 
      type number
    ),
    IdentifyCurrentDate    = Table.SelectRows(InsertYearOffset, each ([Date] = CurrentDate)),
    CurrentFiscalYear      = IdentifyCurrentDate{0}[Fiscal Year],
    InsertFYoffset         = Table.AddColumn(
      InsertYearOffset, 
      "FiscalYearOffset", 
      each Number.From(Text.Range([Fiscal Year], 2, 2)) - Number.From(Text.Range(CurrentFiscalYear, 2, 2)), 
      type number
    ),
    InsertCompletedWeek    = Table.AddColumn(
      InsertFYoffset, 
      "WeekCompleted", 
      each [WeekEnding] < Date.From(Date.EndOfWeek(CurrentDate)), 
      type logical
    ),
    InsertCompletedMonth   = Table.AddColumn(
      InsertCompletedWeek, 
      "MonthCompleted", 
      each [MonthEnding] < Date.From(Date.EndOfMonth(CurrentDate)), 
      type logical
    ),
    InsertCompletedQuarter = Table.AddColumn(
      InsertCompletedMonth, 
      "QuarterCompleted", 
      each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), 
      type logical
    ),
    InsertChangedType      = Table.TransformColumnTypes(
      InsertCompletedQuarter, 
      {
        {"Year", Int64.Type}, 
        {"QuarterOfYear", Int64.Type}, 
        {"MonthOfYear", Int64.Type}, 
        {"DayOfMonth", Int64.Type}, 
        {"DateInt", Int64.Type}, 
        {"DayOfWeek", Int64.Type}, 
        {"ISO Weeknumber", Int64.Type}, 
        {"WeeknYear", Int64.Type}, 
        {"MonthnYear", Int64.Type}, 
        {"QuarternYear", Int64.Type}, 
        {"Fiscal Period", Int64.Type}, 
        {"WeekOffset", Int64.Type}, 
        {"MonthOffset", Int64.Type}, 
        {"QuarterOffset", Int64.Type}, 
        {"YearOffset", Int64.Type}, 
        {"FiscalYearOffset", Int64.Type}
      }
    )
  in
    InsertChangedType

Hope this helps.

Cristian

3 Likes
9

Only after posting this I was looking for the Software/Tools category to let everyone know about https://powerqueryformatter.com/ and I saw this :blush:

Still … I think it’s helpful and more people should know about it .

Cheers,

Cristian

1 Like