Forum Discussion
Helper IIILOOKUPVALUE - 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
Community Championyosemite ,
First you group all your rows by week and created a new column with a grouped table column.
Table.Join(
#"Table (2)", "Employee Name",
Table.RenameColumns([Rows],
{"Employee Name", "EmployeeName"}),
"EmployeeName", JoinKind.LeftAnti)
Did I answer your question? Mark my post as a solution!
Ricardo
Helper IIIcamargos88 I got to this part. See, my table1 and table2 contains many columns. For simplicity, I only posted 1 and 2 columns for the table.
What I'm trying to understand right now is do I list all the column names in table1 and table 2? See highlighted text in red. Also, where did you get 'EmployeeName' when the column names in both tables in PBIX are 'Employee Name'.
Table1.Join(
#"Table2", "Employee Name", Can I include more columns from Table 2?
Table1.RenameColumns([Rows],
{"Employee Name", "EmployeeName"}), Can I include more columns from Table 1?
"EmployeeName", JoinKind.LeftAnti)
- yosemite
camargos88 You are awesome! Thanks for your help.
- camargos88
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!
Ricardo - camargos88
yosemite ,
Doesn't matter how many columns you have. This code just join both tables using the key column "Employee Name".
The "EmployeeName" is just a renamed column to join it.
Ricardo
- yosemite
I'm getting an error Expression.Error: The name 'RollCallList_Test.RenameColumns' wasn't recognized. Make sure it's spelled correctly.
= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Join(
#"ABCEmployees", "EMPLOYEE_ID",
RollCallList_Test.RenameColumns([Rows],
{"EmployeeNo", "EmployeeName"}),
"EmployeeName", JoinKind.LeftAnti)) - camargos88
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)) - yosemite
Hello 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.
- camargos88
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
- yosemite
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. 😣
- yosemite