Forum Discussion

Centaur's avatar
Centaur
Helper V
2 years ago
Solved

Return Records NOT IN

Hello experts,

 

I have a file:  PmtProposal.xls

I also have 3 excel files:  tbl1, tbl2, tbl3

I need to show the records from PmtProposal that are NOT IN tbl1, tbl2, tbl3

the match criteria is on invoice no and invoice amt (not company name).

 

I am not sure what I am doing wrong but the attached power query is returning records that are both IN and NOT IN.

My production file is thousands of records and the attached is a simple example with only 7 records. 

I attached the Power Query file (link below since I cant attach a file) and the associated files but I think the linked files path would need to be updated in the PQ. 

hyperlink to my OneDrive: PQNotIn.zip 

 

Grateful for the assistance. 

My knowledge of PQ is limited.  

thank you

 

Need Help
Tips and Tricks
  • Centaur's avatar
    Centaur
    2 years ago

    I see what my issue was.  I applied a lowercase to the invoice number and it changed the format of the column.  After I removed the lowercawe format, the output was correct.  I know PQ is case sensitive so that is why I put that format on the column but in this case it was the cause of the problem.  

     

1 Reply

  • Centaur's avatar
    Centaur
    Helper V
    2 years ago

    I see what my issue was.  I applied a lowercase to the invoice number and it changed the format of the column.  After I removed the lowercawe format, the output was correct.  I know PQ is case sensitive so that is why I put that format on the column but in this case it was the cause of the problem.