Power BI: Power Query reference another table (dates/ID)

[BrianDWalker]

Hi,

I'm experiencing a problem while trying to reference another table. Here is the problem I'm running into:

I have a table of sales data where it has various columns, but the ones that I'm focusing on is the Employee UID and the transaction date. I have another table that shows employee info: Name, email, UID, Position Start Date, & Position End Date. The problem I'm running into is that I don't want to query sales data from when they were in their previous role or next role. Only while they were active in this organization. I'm not exactly sure how to create the formula in Power BI: Power Query. I tried the following, but doesn't work:

= Table.SelectRows(#"Filtered Rows1", each [transaction_date] >= #date(Date.Year(EmployeeHistory[position_start_date]),Date.Month(EmployeeHistory[position_start_date]),Date.Day(EmployeeHistory[position_start_date])) and [transaction_date] <= #date(Date.Year(EmployeeHistory[position_end_date]),Date.Month(EmployeeHistory[position_end_date]),Date.Day(EmployeeHistory[position_end_date])) and List.Contains(EmployeeHistory[uid], [uid] ))

I'm going to put below an example of the data. In the show column, I indicate whether or not I want it to show or not.

EmployeeHistory Table

UID	Position State Date	Position End Date
AB123C	10/15/2020	12/31/9999
EF456G	2/1/2019	5/15/2021
KL789M	3/15/2021	12/31/9999

Sales Table

Transaction Date	UID	Quantity	Amount	SHOW?
1/1/2021	EF456G	5	$100.00	YES
2/10/2021	AB123C	7	$250.00	YES
3/12/2021	KL789M	6	$300.00	NO
4/21/2021	AB123C	2	$400.00	YES
5/30/2021	EF456G	3	$95.00	NO
6/5/2021	KL789M	4	$75.00	YES

If someone could assist me with how I can accomplish this in Power BI Power Query, that would be extremely helpful!

[Vera_33 · Accepted solution]

Hi @BrianDWalker 

You are identifying whether the [Transaction Date] is between [Position State Date] and [Position End Date] for each UID.

Your way is to select the rows then do List.Contains

Table.AddColumn(yourPreviousStep, "Show",(x)=>List.Contains(
    Table.SelectRows(EmployeeHistory, each [Position State Date]<=x[Transaction Date] and [Position End Date]>=x[Transaction Date])[UID],x[UID]))

If you have many rows, it can be slow. So merge the table first

    #"Merged Queries" = Table.NestedJoin(yourPreviousStep, {"UID"}, EmployeeHistory, {"UID"}, "EmployeeHistory", JoinKind.LeftOuter),
    #"Added Custom1" = Table.AddColumn(#"Merged Queries", "Custom", each [Transaction Date]>=[EmployeeHistory][Position State Date]{0} and [Transaction Date]<=[EmployeeHistory][Position End Date]{0})