Forum Discussion

yosemite's avatar
yosemite
Icon for Helper III rankHelper III
6 years ago
Solved

LOOKUPVALUE - Need Help

Hello,   I need to perform a look up of Table1[Column1] in Table 2[Column1] and return values that do not exist in Table1[Column1].   Appreciate any help.   Thank you.
  • camargos88's avatar
    camargos88
    6 years ago

    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!
    Ricardo

  • camargos88's avatar
    camargos88
    6 years ago

    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

yosemite's avatar
yosemite
Icon for Helper III rankHelper III
6 years ago

This didn't work. Thanks for trying.

 

Since my goal is to get a list of employees that do not exist in Table1 by Week, I would like my merged table to return the following:

 

WeekEmployee Name
Week 2Employee2
Week 2Employee4
Week 3Employee2
Week 3Employee3
Week 3Employee4
Week 3Employee5

 

nandukrishnavs's avatar
nandukrishnavs
Icon for Community Champion rankCommunity Champion
6 years ago

yosemite 

 

Another alternate approach.

 

Table1

Week Employee Name
Week 1Employee1
Week 1Employee2
Week 1Employee3
Week 1Employee4
Week 1Employee5
Week 2Employee1
Week 2Employee3
Week 2Employee5
Week 3Employee1

 

Table2

 

Employee Name
Employee1
Employee2
Employee3
Employee4
Employee5

 

 

 

Present = 
var _count=CALCULATE(COUNT(Table1[Employee Name]))
var _result=IF(ISBLANK(_count),0,_count)
return _result

 

 

Create a matrix visual.

 

 

Now you can export this visual. You will get the below table.

 

Now you can filter zero.

 



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂