Forum Discussion
LOOKUPVALUE - Need Help
Hi yosemite ,
Take a look at this file: Download PBIX
Basically I grouped the values per week and joined with a left anti and filtered nulls.
Did I answer your question? Mark my post as a solution!
Ricardoyosemite ,
Check this new file: Download PBIX
Take a look at the week_missing table on Power Query.
Did I answer your question? Mark my post as a solution!
Ricardo
yosemite ,
Table.RenameColumns is a function, you can change it. You just change the parameters of it.
= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Join(
#"ABCEmployees", "EMPLOYEE_ID",
Table.RenameColumns([Rows],
{"EmployeeNo", "EmployeeName"}),
"EmployeeName", JoinKind.LeftAnti))
camargos88 You are awesome! Thanks for your help.
yosemite ,
Check this new file: Download PBIX
Take a look at the week_missing table on Power Query.
Did I answer your question? Mark my post as a solution!
RicardoHello camargos88 - Just checking if you have an idea why I am not getting any 'null' EmployeeNo after using JoinKind.LeftAnti so I'm unable to filter it out.
I'm going back to your steps and want to make sure I didn't miss anything. This is what I did to group the week:
- Highlight Week column
- Transform > Group By
- Basic > New column name: Type Rows > Operation: All Rows
Then I successfully added the Custom column with the code below.
= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Join(
#"ABCEmployees (2)", "EmployeeNo", Table.RenameColumns([Rows], {"EmployeeNo", "EmpName"}),
"EmpName", JoinKind.LeftAnti))The only problem is when I removed Rows column and expanded Custom column, I didn't find any null EmployeeNo.
yosemite ,
You should get a return with 3 columns:
1 week column
2 employee num (1 from left side / 1 from right side).
If you don't have any nulls it's likely you don't have any employee for this week...it's sounds weird
Can you provide some samples of this week ?
Ricardo
camargos88 Although I didn't have to filter out 'null' values, it worked on this simple dataset; see link below.
My dataset has thousands of employees, and they are not being filtered out. I followed all the steps. 😣