Power Query - Multiple criteria with dates

[fdfordham]

I am having difficulty with merging tables and multiple criteria. I would like to receive "Employee_Rates.True Cost Rate" from matching "Employee_Hours.Employee Name" to "Employee_Rates.Employee Name" and if "Employee_Rates.Start Date" is <= (less than or equal) to "Employee_Hours.Date Worked". I am merging another table into the query and all data is as expected until line #"Merged Queries". After this and #"Expanded Employee_Rates", the data is reduced to one line. The Employee_Hours table has duplicate names with different Start Dates for different True Cost Rates. The following is the code from the editor:

let
Source = Table.NestedJoin(Project_List, {"Project Number"}, Employee_Hours, {"Project Number"}, "Employee_Hours", JoinKind.LeftOuter),
#"Expanded Employee_Hours" = Table.ExpandTableColumn(Source, "Employee_Hours", {"Employee Name", "Date Worked", "Hours Worked "}, {"Employee_Hours.Employee Name", "Employee_Hours.Date Worked", "Employee_Hours.Hours Worked "}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Employee_Hours",{"Project Description ", "Project Number", "Employee_Hours.Employee Name", "Employee_Hours.Date Worked", "Employee_Hours.Hours Worked "}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Project Description ", Order.Ascending}}),
#"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Employee_Hours.Employee Name", "Employee_Hours.Date Worked"}, Employee_Rates, {"Employee Name", "Start Date"}, "Employee_Rates", JoinKind.Inner),
#"Expanded Employee_Rates" = Table.ExpandTableColumn(#"Merged Queries", "Employee_Rates", {"True Cost Rate"}, {"Employee_Rates.True Cost Rate"})
in
#"Expanded Employee_Rates"

I can do this within a spreadsheet, but in Power Query. Hopefully someone can provide some suggestion. And I hope this is enough information.

Thank you!

 

[jorismoerings]

Hi,

Could you share your table - structure for both (to be joined) tables?
Because from the editor lines I can't find the structure to do "Employee_Rates.Start Date" is <= (less than or equal) to "Employee_Hours.Date Worked"

To show the table structure (if possible) please use XL2BB add-in, this will help us preventing to do a type over from a screenshot.
More info on XL2BB to be found here

 

[fdfordham]

I think this is what you are asking for. Thank you for the help.

Employee_Rate Table

Project_True_Cost_Analysis_20210801 - Copy.xlsx
	A	B	C	D
3	Employee Name	Start Date	True Cost Rate	Billing Rate
7	Mary	1/1/2000	$ 57	$ 125
15	Steve	1/1/2000	$ 10	$ 10
16	Steve	5/1/2021	$ 212	$ 300
Employee Rates

Project_Cost 2 Table

Project_True_Cost_Analysis_20210801 - Copy.xlsx
	A	B	C	D	E
1	Project Description	Project Number	Employee_Hours.Employee Name	Employee_Hours.Date Worked	Employee_Hours.Hours Worked
2	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	5/1/2020	1
3	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	4/30/2020	1
4	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	5/5/2020	2
5	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	5/11/2020	2
6	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	5/6/2020	4
7	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	4/29/2020	4
8	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	4/22/2020	4
9	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	4/16/2020	2
10	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	4/23/2020	4
11	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	4/28/2020	2
12	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	4/27/2020	1
13	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	8/19/2021	1
14	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	5/20/2020	4
15	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	8/20/2021	2
16	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	5/27/2020	5
17	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	5/26/2020	3
18	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	5/19/2020	2
19	19016 - McNair Campus - O'Quinn Tower	19-02-004-1	Steve	5/13/2020	4
Project_Cost (2)

 

[jorismoerings]

Hi,

To do this in Power Query is working with a trick to avoid joining tables only on the exact date. To avoid that you would need to extend the rates table with an end date.
So Reform your Employee RAtes table to this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
    GroupbyName = Table.Group(#"Changed Type", {"Employee Name"}, {{"MaxDate", each List.Max([Start Date]), type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Employee Name"},GroupbyName,{"Employee Name"},"GroupedOnName",JoinKind.LeftOuter), 
    #"Expanded GroupedOnName" = Table.ExpandTableColumn(#"Merged Queries", "GroupedOnName", {"MaxDate"}, {"GroupedOnName.MaxDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded GroupedOnName",{{"GroupedOnName.MaxDate", "MaxDate"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "IsLatest", each if [MaxDate] = [Start Date] then "Yes" else "No"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "End Date", each if [IsLatest] = "No" then Date.AddDays([MaxDate],-1)else Date.From(DateTime.LocalNow())),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"End Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"MaxDate", "IsLatest"}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Removed Columns", "Subtraction", each Duration.Days([End Date] - [Start Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Date Subtraction", "Merge Date", each List.Dates([Start Date],[Subtraction]+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Merge Date"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Merge Date", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Start Date", "End Date", "Subtraction"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Employee Name", "Merge Date", " True Cost Rate ", "Billing Rate"})
in
    #"Reordered Columns"

This will extend your Rates table to an extended table with all dates within the validity date in rows.
Join the Changed Rates table with the employee hours worked and your done.

As a side note. This technique depends on having an end date. For all open End dates i used todays date, assuming nobody registers hours worked after today because they actually haven't worked them yet.

This is a link to my testfile

There might be other more efficient solutions but this is quickest i could think of.

Hope this helps you.

 

[jorismoerings]

Addition to previous,

Thought of other more efficient option. A general part of previous applies here as well
Change the query for the Rates Table to this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
    GroupbyName = Table.Group(#"Changed Type", {"Employee Name"}, {{"MaxDate", each List.Max([Start Date]), type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Employee Name"},GroupbyName,{"Employee Name"},"GroupedOnName",JoinKind.LeftOuter), 
    #"Expanded GroupedOnName" = Table.ExpandTableColumn(#"Merged Queries", "GroupedOnName", {"MaxDate"}, {"GroupedOnName.MaxDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded GroupedOnName",{{"GroupedOnName.MaxDate", "MaxDate"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "IsLatest", each if [MaxDate] = [Start Date] then "Yes" else "No"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "End Date", each if [IsLatest] = "No" then Date.AddDays([MaxDate],-1)else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"End Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"MaxDate", "IsLatest"})
in
    #"Removed Columns"

and change the join to this:

let
    Source = Table.NestedJoin(#"Employee Hours", {"Employee_Hours.Employee Name"}, #"Employee Rates (2)", {"Employee Name"}, "Employee Rates", JoinKind.LeftOuter),
    #"Expanded Employee Rates" = Table.ExpandTableColumn(Source, "Employee Rates", {" True Cost Rate ", "Billing Rate", "Employee Name", "End Date", "Start Date"}, {"Employee Rates. True Cost Rate ", "Employee Rates.Billing Rate", "Employee Rates.Employee Name", "Employee Rates.End Date", "Employee Rates.Start Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Employee Rates", each ([Employee_Hours.Date Worked] >= [Employee Rates.Start Date] and [Employee_Hours.Date Worked] <= [Employee Rates.End Date]) or ([Employee_Hours.Date Worked] >= [Employee Rates.Start Date] and [Employee Rates.End Date] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Employee Rates.Employee Name", "Employee Rates.End Date", "Employee Rates.Start Date"})
in
    #"Removed Columns"

Shorter and does the trick:

Testfile with both options here