Compare Two Columns with Microsoft Excel Power Query | Excel Formula Hacks

hello and welcome to compare two columns in Excel  with power query my name's Jeff I'm glad you're   here let's just jump right in all right in this  short video we're going to use power query to   compare two lists we're going to get warmed up  by comparing some single column lists and then   we're going to do some multiple column tables  the fastest way is just to head straight to   excel exercise one all right in this exercise we  basically have two lists and we want to find out   which items are on both lists and which items  are only on one list so what we're going to do   is we're going to load this into Power query by  going to data from table range all right with   that data loaded into Power query we're simply  going to close and load two a connection only   query and click OK with list one loaded it's time  to load list two once again we're going to close   and load two a connection only query and click  ok now we're going to do the comparison get data   combine queries merge here we pick list one and  then list two now we need to identify the lookup   column which in this case is the list one and list  two columns so we just pick both of those now in   the join kind drop down this is where we get to  determine what type of comparison do we want to   do for example if we pick inner it's going to  return a list of all of the matches found in   both lists however we can also find the rows that  are only in the First Column or only in the second   column so you can choose this accordingly based  on what you're working on in this case I want to   find which items appear in both lists so I'm going  to select inner and then I click OK I don't need   this list to columns so I'm just going to remove  it and I'm going to close and load and I'm going   to send the results to a table and I'm going to  put it right here so I can check it out and now we   have a list of items that appear on both lists if  I wanted to do some exception reporting and find   the items that appeared in list 1 only no problem  I could easily modify this query I can click the   source gear icon and I can change the join kind  to left anti this is rows only in the first and   I click OK and now I can close and load and now I  have a list of items that appear only in list one   on the other hand if I want to see which items are  in the list two only I can modify our query click   on the source gear icon and change the join kind  to right anti and click OK in the updated results   I'm going to expand this and click OK since this  removed column step no longer applies I'm going to   remove it and instead I'm going to remove the list  one column now I can close and load and I have a   list of items that appears in list 2 only let's go  to the next exercise exercise two now I'd like to   find out which items in table 1 also appear in  table two so again I'm going to get this table   into Power query by going to data from table range  now that that's loaded I'm going to close and load   two I'm going to create a connection only query  and click ok I'll do the same thing with table   two close and load 2 connection only query with  those two tables in power query I'm going to go   ahead and get data combine queries merge I'm going  to pick table one and then table two again I need   to pick the columns that must match in the same  order product ID option cost and I'm holding down   shift I could also hold down control product ID  option cost now the key thing is that these need   to be selected in the same order so product ID  is one and then option two and then cost three I   need to make sure those line up so product ID one  option two cost three now depending on on what I'm   working on I can determine the correct join kind  inner will show me which rows appear on both and   if I wanted to do a different kind of comparison  I could pick a different join kind in this case   I pick enter and I click OK I don't need this  table two columns so I'm just going to remove   it and I'm going to close and load two and I'll  place this on a new worksheet and click ok now   these are the items that appear on both lists but  what if we actually wanted to identify items that   match product ID and option and then I wanted to  compare the cost for example cg231 small 6 is not   found on my results however cg231 small does exist  in the list too but it didn't get matched because   the cost was different so what if I wanted to  compare on product ID and option columns and   then compare the two cost amounts that leads us  to exercise three exercise three the first step   is to get this into Power query so I'm going  to go to data from table Range close and load   2 connection only query click OK do the same  thing with table 4 close and load 2 connection   only query get data combine queries merge we  select table three and table four this time the   columns that we're going to compare are product  ID and option product ID and option again I want   to look at only the matching rows and click OK  this time I want to expand this column and I only   really want to bring in the cost now to compute  the difference I'll select the first cost column   and the second cost column and then I'll click  add column standard subtract and now I get the   difference if I wanted to show the exceptions I  could simply filter out zeros then I'm going to   close in load two a table in a new worksheet and  click OK and now I see the records that appear in   both tables but have different costs and that's  going to help me with my reconciliation and I can   go update the values accordingly hey hopefully  this helps thanks for joining me have a great   day hey Excel user if you ever need to create  summary reports check out my pivot table for   beginners video it starts at the beginning and  shows how to store the data transactions in a   table and then how to summarize those transactions  with a pivot table report I hope it helps unlock   the incredible power of pivot tables [Music]  this video is a production of excel University foreign [Music]

Channel: Excel University

Views: 6,164

Keywords: chatgpt, chat gpt, how to use chatgpt, microsoft excel, excel, excel tips, statistics, excel tips and tricks, microsoft excel tutorial, excel tutorial, excel university, jeff lenning, excel filter function, business, spreadsheets, excel formulas, power query, compare two columns in excel, excel formula hacks

Id: 7Uxx0QXfYEw

Length: 5min 30sec (330 seconds)

Published: Wed Jul 19 2023