Power Query: Lookup to See If a Holiday Falls Within a Work Week

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,214
Office Version
  1. 365
Platform
  1. Windows
I have a table with a Date column that has the beginning of each (U.S.A) work week, which starts on a Monday, like 7/2/2018, 7/9/2018, 7/16/2018, etc. In another table I have a list of U.S.A Holidays, for example 7/4/2018 (Independence Day), 9/3/2018 (Labor Day). I would like a custom column to do a lookup to see if a Holiday falls in the beginning of each work week date then return 8 otherwise show a blank/null.

For example, taking the dates mentioned above, I would like to see next to the work week 7/2/2018 in a custom column an 8 because the holiday on 7/4/2018 falls into that week. I should see a blank/null for 7/9/2018, 7/16/2018, since the holiday does not fall within those weeks.

This is almost like a approximate match lookup that I'm trying to do in Power Query. Can someone help me on how to go about solving this?

Thanks!
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
I have created two tables, one named Dates and another called Holidays:

Js6TnQX.png





With the below M you will get the following table:



<date.adddays(dt,7) =""="" }="" )="" ),="" add="" column="" addcol="Table.AddColumn(TransformDatesListType," "holiday="" check",="" each="" if="" fltrlist([date],holidayslist)="" then="" null="" else="" 8,="" int64.type)=""
<date.adddays(dt,7)

<date.adddays(dt,7)
<date.adddays(dt,7) =""="" }="" )="" ),="" add="" column="" addcol="Table.AddColumn(TransformDatesListType," "holiday="" check",="" each="" if="" fltrlist([date],holidayslist)="" then="" null="" else="" 8,="" int64.type)=""
<date.adddays(dt,7)
Code:
let


    // load dates list
    DatesList = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],


    TransformDatesListType = Table.TransformColumnTypes(DatesList,
                                                        {
                                                          {"Date", type date}
                                                        }
                                                        ),
    
    // load holidays
    HolidaysList = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Holidays"]}[Content],
                                              {"Holidays", type date}
                                             )[Holidays],


    
    FltrList = (Dt as date, Lst as list) as logical => 
            List.IsEmpty(List.Select(Lst, each List.AllTrue({
                                                             _>= Dt,
                                                             _<date.adddays(dt,7)
                                                            }
                                                           )
                                     )
                       ),
    // add column
    AddCol = Table.AddColumn(TransformDatesListType,
                             "Holiday Check",
                             each if 
                                      FltrList([Date],HolidaysList)
                                  then
                                      null
                                  else
                                      8,
                             Int64.Type)
                            


in
    AddCol





uYDoWrX.png
</date.adddays(dt,7)
</date.adddays(dt,7)
</date.adddays(dt,7)></date.adddays(dt,7)
</date.adddays(dt,7)
</date.adddays(dt,7)>
 
Last edited:
Upvote 0
I am having troubles posting the M code well formatted, so below is a screenshot of the code

and HERE you can download the file



99ij2PT.png

Thanks. However, I found a much easier solution using a custom column with the following Power Query function, where [Holiday Date] is my date field name and the 1 means starting on a Monday as the beginning of the week:

Code:
Date.StartOfWeek([Holiday Date],1)

Then I created a second custom column and just put
Code:
=8
so it fills for every row. Subsequently, I did a merge with the my Date table and my Holiday table and where they matched (left outer join)and I simply selected to show only where they have a 8.
 
Last edited:
Upvote 0
Thanks. However, I found a much easier solution using a custom column with the following Power Query function, where [Holiday Date] is my date field name and the 1 means starting on a Monday as the beginning of the week:


That is a valid alternative. I did not think about using Date.StartOfWeek, I guess as good practice it would be good to go through the M function Reference whenever we try to solve a problem, just to remind us of the options we have got, well done
 
Upvote 0
That is a valid alternative. I did not think about using Date.StartOfWeek, I guess as good practice it would be good to go through the M function Reference whenever we try to solve a problem, just to remind us of the options we have got, well done

Precisely. I'm beginning to think that reviewing Power Query functions is a good starting point before missing with M code. Thanks again.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,595
Members
452,656
Latest member
earth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top