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

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

Hi yosemite ,

 

Try this one:

 

 

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     🙂

     

     

     

     

     

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    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

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

      camargos88 - Thank you so much. I feel like I'm getting closer.

       

      Could you walk me through how you 'Added Custom' (Step 6) and  joined with a left anti?

      • camargos88's avatar
        camargos88
        Icon for Community Champion rankCommunity Champion
        6 years ago

        yosemite ,

        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