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

[legalhustler]

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!

 

[VBA Geek]

I have created two tables, one named Dates and another called Holidays:

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)

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

</date.adddays(dt,7)
</date.adddays(dt,7)
</date.adddays(dt,7)></date.adddays(dt,7)
</date.adddays(dt,7)
</date.adddays(dt,7)>

 

[VBA Geek]

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

 

[legalhustler]

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

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:

Date.StartOfWeek([Holiday Date],1)

Then I created a second custom column and just put

=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.

 

[VBA Geek]

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

 

[legalhustler]

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.