Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Conditional Table merge problem

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_codeWeekStartOfWeekPosition
1131-12-2018Warehouse
127-1-2019Warehouse
1314-1-2019Warehouse
1421-1-2019Truck
1528-1-2019Truck
164-2-2019Truck
1711-2-2019Truck
2131-12-2018Warehouse
227-1-2019Warehouse
2314-1-2019Warehouse
2421-1-2019Truck
2528-1-2019Truck
264-2-2019Truck
2711-2-2019Truck
3131-12-2018Warehouse
327-1-2019Warehouse
3314-1-2019Warehouse
3421-1-2019Truck
3528-1-2019Truck
364-2-2019Truck
3711-2-2019Truck

 

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

Employee_codeStartEndPosition
131-12-201815-1-2019Warehouse
116-1-20191-1-2099Truck
231-12-20181-1-2099Ship
331-12-20181-1-2099Truck

 

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!

3 REPLIES 3
v-gizhi-msft
Community Support
Community Support

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.

L-1.PNGL-2.PNG

And the test result.

L-3.PNG

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
Not applicable

Fantastic! Thanks so much, it works now.

Anonymous
Not applicable

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.