Compare Tables for Unmatched Records in Excel with Power Query
If you want to compare table that have records that do not match or unmatched, Power Query in Excel has a great function with the merge feature and ANTI JOIN. This occurs in SQL when two tables returns rows from the first table where no matches are found in the second table. It's consider the opposite of a semi-join when in two tables the result returns rows from the first table where one or more matches are found in the second table. In this example an anti-join is finding records where there's no match between both tables. However in Excel Power Query, there is not anti join option when using the merge feature. However the workaround in to use the left and right anti join features and then append the results. Confused? Well check out the video to see how it could be done.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to / doughexcel
#excel
#msexcel
#doughexcel
Пікірлер: 22
Nice, very helpful. thanks Doug.
@DougHExcel
4 жыл бұрын
Hi Luciano...you're welcome, glad you liked!
Thank you very much! I'm in the process of learning PQ and it's simply amazing. This idea will, for sure, be of great help on a routine task I have to go through every month at my work. You're a savior ;-)
@DougHExcel
3 жыл бұрын
Glad it was helpful!
Thanks. Your Power Query series is the best.
@DougHExcel
4 жыл бұрын
Hi Pasa Man -_- lml, thanks for the kind words!
Thank you Doug H
This is great but how do you do if multiple tables?
Thank you 👌
@DougHExcel
3 жыл бұрын
You’re welcome!
Nice work 👍
@DougHExcel
4 жыл бұрын
Thanks FRANKWHITE1996!
Thank you so much… my problem is solved ❤
@DougHExcel
Жыл бұрын
Welcome 😊
Can you show one matching 2 columns per table? Where for eg. If the same person has more than one email address like a secondary one and we want to get the rows that have both email addresses?
@DougHExcel
3 жыл бұрын
maybe this will give a idea kzread.info/dash/bejne/mK6asJptm6fXl7g.html
I want to show a MATCH & MISS MATCH text message beside the column how to do it.?
@DougHExcel
Жыл бұрын
maybe try the left outer join and add a helper conditional column. kzread.info/dash/bejne/eaCBqKSHgbi3gZs.html kzread.info/dash/bejne/eqCDsZNxpbbWkps.html
is it last-----------I am learning from it.
@DougHExcel
4 жыл бұрын
Hi Khalid Majeed, thanks for the comment!
Lets assume that we have two components of salary i.e. Basic and Conveyance an employee receives PKR100 and 200 respectively for these two components in the month of April and the same employee receives USD200 and USD100 respectively for the same components in the month of May. Can Power BI DAX give me the information in another table under column Basic and Conveyance as 100 and -100 for the same employee? Please note that we have more than 14,000 employees and every month I have to identify the same by looking for the variance in payroll and identifying the staff who receive different payments as compared to last month. Thank you for your help! if there are more information required I can share the sample file through email.
@DougHExcel
3 жыл бұрын
Hi Riaz Uddin, sorry 🙁....but try a post on the mrexcel.com forum!