Conditional Table merge problem

[Anonymous]

Hi guys,

So I've found this solution that should work, but doesn't get me the results I needed.

My goal is to get the correct position for each employee during that week. So for every row, it should evaluate on which position that employee is working that week.

I have two tables:

- Table "Employee": Employee_code, week, first date of that week, position [to be filled by the merge]

Employee_code	Week	StartOfWeek	Position
1	1	31-12-2018	Warehouse
1	2	7-1-2019	Warehouse
1	3	14-1-2019	Warehouse
1	4	21-1-2019	Truck
1	5	28-1-2019	Truck
1	6	4-2-2019	Truck
1	7	11-2-2019	Truck
2	1	31-12-2018	Warehouse
2	2	7-1-2019	Warehouse
2	3	14-1-2019	Warehouse
2	4	21-1-2019	Truck
2	5	28-1-2019	Truck
2	6	4-2-2019	Truck
2	7	11-2-2019	Truck
3	1	31-12-2018	Warehouse
3	2	7-1-2019	Warehouse
3	3	14-1-2019	Warehouse
3	4	21-1-2019	Truck
3	5	28-1-2019	Truck
3	6	4-2-2019	Truck
3	7	11-2-2019	Truck

- Table "Position": Employee, position, start date, end date

Employee_code	Start	End	Position
1	31-12-2018	15-1-2019	Warehouse
1	16-1-2019	1-1-2099	Truck
2	31-12-2018	1-1-2099	Ship
3	31-12-2018	1-1-2099	Truck

As you can see employee #1 shows up fine in my first table and switches positions perfectly. However the pattern repeats itself, so #2 doesn't show "Ship" at all. #3 is also wrong.

I've used the following formula in my "Employee" table for column "Position" that looks if my StartOfWeek is between my Start and End date, and should result the matching position for that row:

= Table.AddColumn(#"Employee", "Position", each Table.SelectRows(Position, (Position) => [StartOfWeek]>=Position[Start] and [StartOfWeek]<=Position[End])[Position]{0})

What am I doing wrong here? Thanks!

[v-gizhi-msft]

hi,

According to your description, please add ‘Employee_code’ filter to your original formula.

= Table.AddColumn(#"Employee", "Position", each Table.SelectRows(Position, (Position) => [StartOfWeek]>=Position[Start] and [StartOfWeek]<=Position[End] and [

Employee_code] = Position[Employee_code])[Position]{0})

Here are my test tables.

And the test result.

Here is my pbix file.

pbix 

If you still have questions about it, please for free to let me know.

Best Regards,

Giotto Zhi

[Anonymous]

Fantastic! Thanks so much, it works now.

[Anonymous]

Hello everyone,

It's been a while but I have found the time to implement this solution in my real-life PBI file. However, when I insert the formula I get the following error:

"Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
    [List]"

The formula I'm using is:

= Table.AddColumn(#"Changed Type", "Functie2", each Table.SelectRows(PowerBI_Medewerker_org_eenheid_en_functie, (PowerBI_Medewerker_org_eenheid_en_functie) => [StartDate]>=PowerBI_Medewerker_org_eenheid_en_functie[BeginDatum] and [StartDate]<=PowerBI_Medewerker_org_eenheid_en_functie[EindDatum] and [EmployeeNr] = PowerBI_Medewerker_org_eenheid_en_functie[EmployeeNr])[Functie]{0})

When I change EmployeeNr from the "org en eenheid" table to another column, the formula works (with some errors) but it's not the correct column. I really need to make a match on EmployeeNr.

Please help!