Easily Compare Multiple Tables in Power Query

comparing tables and power query is typically done with mergers however with list functions you can simplify this task into one or two steps which is particularly helpful if you're comparing three or more tables in this video Phil's going to show you how to use list functions to compare three tables to a master table and I promise it's dead easy comparing tables in Excel is a common task you may need to identify items that are the same different or missing from columns in these table in power query table columns are a special data type called lists and you can compare these lists using table merges but merging can only be done on two tables at a time if you need to compare three or more tables then using list functions is the way to do it it does require some manual M coding and if the thought of that puts you off it's not hard the three examples I'll show you contain only two lines of code each and do what can take multiple lines using table merges or a pen let's look at our source data I've got four tables the first contains the names of some imaginary staff members and an ID code assigned to them and the other three tables show the names of staff that attended three different days of training that were arranged for them my tasks are to work out which staff attended every day of training attended at least one day of training or attended no days I've already loaded three of these tables into Power query so let's load the last one one I'll click into this table can right click and then get data from sheet and then up here under close and load click on the drop down click on close and load to and then choose only create connection let's jump back into the power quy editor so to work out who attended every day of training I have to find out who appears in every table for training days 1 2 and three if you were using table merges then you do an inner join on the training one table and the training two two table to return only matching rows you then have to do another inner join on the result of the first join and the training three table if your eagley you would notice that a greater Johnson in the first row here of training two table is all in lower case so when you're doing those merges those joins you have to make them case insensitive and to do that you need to make them fuzzy joins but you can do all of this in one line using the list. intersect function you're not sure what that is well let me show you first of all let's create a new blank query I'm going to call it function reference and then up here in the formula bar I'm just going to type equals hash shared and this gives me a list of all the functions available in power query I'll turn this into a table then I'll filter it just for the list functions and then just sort it so we want to use list. intersect so if we just scroll down to that and then click here you can see it says it returns intersection of the list values found in the input lists which basically means if you remember your set theory from school it just returns everything that's common in the lists that you input to the function let's get on with creating that query so another new blank query call it attended all days and in the formula bar we type equals list do intersect and we Supply the lists or the table columns inside uh curly braces and the lists or the columns that we want to compare are the name columns from each of the training one two and three [Music] tables now I want to make make sure that this comparison is case insensitive so I'll Supply one more parameter which is a comparer basically just says to the function make sure that you do your text comparisons case insensitive so I'll use compare. ordinal ignore case and that's it that's your code and there are the seven people who attended all days of training if you're happy with just that list you can stop there but you probably want to filter the staff table to return all the columns including the ID so if we go back to our attended all day's query we need to add one more step over here under applied steps right click on Source click on insert step after and then rename that I'm going to call it tab short for table what I'm going to do here is use the table. select rows function to filter the staff table using this list uh to return only these people and their IDs now that might sound complicated but it's really not I'll show you how it works by clicking on the staff table if we filter this name column let's say we take out Barbara here you can see in the formula bar that it's used the table. select rows function and it's filtered the table called change type because that's the previous step and the result of that step is a table and it's saying that for each name only show me the rows that are not equal to Barbara tarz I'm going to use exactly the same function and I'm just going to modify it myself so it Returns what I want and an easy way to do that is copy all of this out back in attend it all days in the step called tab that we just created delete all of this and paste in the code we just copied we want to select rows from a table which table well it's the staff table so delete that and put in staff how are we going to filter it well we want to return each row in the staff table where the name is one of the names in this list here and to do that we'll use the list. contains any function how does this function work well it will filter the name column of the staff table and it will filter it based on the names in the source list of this particular query there we have the filtered table right let's work out who attended at least one day of training I'll create another new blank query and call it attended at least one day and how do I do this well it involves checking every day and seeing who is there at least once obviously you could do this by combining the three tables training one training two and training three by appending the queries and then removing any duplicates or you could just use use the list. UN function with our attended at least one day query selected in the source step up here in the formula bar we just need to enter equals list. Union and specify the name Columns of the training one two and three [Music] tables and let's make this a cas in sensitive comparison by specifying the ordinal ignore case comparer and we have 16 people who attended at least one day of training as with the previous query we can filter the staff table to return all the columns from that staff table and it's exactly the same line of code let's go back to attended all days we can copy out this code here back in our attended at least one day query create a new Step rename it to Tab and paste in the code we just copied finally to work out who attended no training days and needs to be reprimanded we need to start with a full list of staff and then remove the names of those who attended on each of the three days using table merges you can do this with left anti joins but you need to do three separate joins to get the final list or you can do it with one line using list. difference create a new query rename it attended no days in the formula bar we want to use list. difference and I want to find the difference between the staff table and the list of the people who attended at least one day of training now we've just done that so I can reuse the code for that query which is using list. Union giving us three people who attended no days of training you can do a quick sanity check here because if you add up the number of people who did no training so three and those who did at least some training which is 16 then you should get the same number as your total list of Staff which is 19 which is what we get and as with the previous two queries to filter the staff table and return all columns we can use the same code so just back here in the tab step grab this code copy in attended no days create a new Step rename it Tab and just paste in my code and that's it I've worked out all three of the things I want I can now close and load these into Excel okay I've shown you how to use list functions to compare lists and filter tables and in the sample file that I've created and you can download I've also included the queries that use merges and append so you can have a look at these and compare both methods if you're not sure about learning M functions like list or intersect I'd really encourage you to read through the Microsoft function definitions and become familiar with them and try them out although I do have a programming background and I didn't wake up one day and suddenly know these M functions I had to put the time in to learn them and see how they worked just like when you first started using Excel you didn't know how to use vlookup or Su if so if you're at all hesitant about learning M functions just dive in and give it a go I hope you enjoyed learning about list functions you can download the file for this lesson from the link here if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for [Music] watching

Channel: MyOnlineTrainingHub

Views: 91,972

Keywords: power query, list functions, compare tables

Id: kjeR7nZ3Clw

Length: 11min 11sec (671 seconds)

Published: Thu Aug 05 2021