in this video i'm going to show you how to pull data from another file in excel based
on a criteria so i'll be using this source file obviously you can use any source file you want
there are a couple of things i need to mention about your source file so there are a couple
of ways to go about this if your source file is laid out like this so you have your headers in the
first row and your data goes row by row like this then really nothing else you have to do in the
source file you could just use it as is however if you have situations when your tables are
i don't know like this in the middle of the spreadsheet and then you have a bunch of other
things here other things here etc maybe another table then in that case you want to make sure
that you take the source data in your source file whatever you have here and convert it to a table
so you'll go under insert and click table and then click my table has headers which refers to
the first row in this press ok and that's going to convert this to a table and then you want
to go on top here table design now if you hate this design you can go in here and remove that
none to get rid of that design you can also keep it but the main thing you want to worry about here
is just name this table here on the left so just name it anything you want no special characters
no spaces so you could just call this people or something it doesn't matter just remember what you
call this now in my case i'm not going to do this i'm just going to not save any of the stuff i did
and i'm just going to use the source file which didn't have any extra rows or columns or anything
you want to make sure you close the source file before you start doing this
so i have the other file closed now i have my new file in this file i want to
basically pull some information based on criteria and what i'm going to do
actually let's bring that file back for one second so i can explain it what i'm going to do here
so i'm going to just take this gender column and i'm going to pull just let's say female customers
from this list again you could use any criteria you want i'm gonna go with that by the way this
has a column name missing so you probably don't want that
happening so make sure you have some unique column names but i'm going to leave that as is to see how
excel deals with that anyways just for testing
so now i'm going to go to my new file where i need to pull that data go to data section right here
on top and under data section i'm going to go get data and then do from file and then from workbook
and at this point i'm gonna just pick that file this was that file i just had open hit import
and this is what we're gonna get on our screen so as you can see we get this pop-up so what
this is going to show you it's going to show you all the tabs that exist in that excel file
it would also show you all the tables so if you had that second case that i've mentioned
at this point you would just click on that table name in this list as a source in my case i'm
just going to choose the worksheet this one it should give us a little preview right in here
and then i'm going to click on this transform data in older versions of power query this may
be called like added or something so if that's the case just click on that
so basically you're gonna get this window where we can basically do some transformations on this data
and all i'm gonna do i'm just gonna go to this gendered column open the filter and just
pick female press okay and you should see how that filters to just female and at this point
i can do close and load and close and load and that's going to create another worksheet in this
file and it's only going to pull female let's call them clients in this case from that list
so if you make updates to your original file then what you want to do you want to just click
on this refresh on the right and if you have new records it will basically just pull them
and you would get them in this table again another thing to remember if you close this on the right
and you want to get that back you can get it back by going under data and other data you have this
queries and connections and that's where it is so that's that for a simple thing like that so
you could obviously filter by more columns if you wanted to but i want to get this a little
more interesting so let's say we want to have some sort of option here when we select whether we want
to pull female or male customers and based on that selection we'll pull the data from that other file
so to do something like that let me just first create some space above this
and what i'll do i'll just have a dedicated cell i'll call it gender so you want to make sure you
have some sort of name on top here because we need to create a table out of this and for this to be a
table it needs a column name and just below we'll have the option so let's say we want to choose
mail this time so i'm going to take this and i want to use that in my queries somehow and
to do that i'm going to need to convert this to a table so i'll select this go under insert
click on table and check this my data has headers my table press ok
and we want to make sure we go here under design you can choose different design you
can also remove it as i said before i'll pick this one and we want to make sure we give this
table a good name because right now it's called table two so i'm just gonna call this our choice
now to use this table i'm gonna click on this go to data section again and under data section
i'm gonna click from sheet if you don't have it here for whatever reason you should be able
to get it by going under get data and here you should have that option i don't know where is it
there it is from other sources see we have from sheet and that will be the same thing
so what that's gonna do it's gonna create another connection to that second table see we have this
first connection to customers table and then we have this second connection to this new
table now i want to be able to use this value in this table that says mail
in this filter right in here and to be able to do that first i'm going to go back to this table
and i'm going to convert this to a function so to do that i'm just going to right click on this name
on the left and do create function now you'll see it says there are no parameters
that's fine for us we're going to create the function anyways and it's going to ask what
do you want this new function to be called i'm going to call this get choice you can call it
literally anything you want just no spaces no special characters and make sure you remember
this name because you're going to need this so i'm going to press ok and this is going to be now that
function now the problem with this function is going to be when we execute this function
if i go back to this table it's basically going to give us a table instead of giving us a single
value now before we get into modifying that let's actually go back and use this function on
this table to see what happens so this is the table we had originally so i'm going to take
this customers table which is currently filtered to female go on top here to this advanced editor
and right in here if you look through all these lines there is going to be one line that shows
us that filter see it says in gender column we want to basically filter it to female in
codes so i'm going to remove that female that's hard coded in quotes and replace it by that get
choice which is that function that i've created and because it's a function i have to have open
close parentheses like that so i'm replacing that hard coded value with this function
that should go and pull the gender so i'm going to do that and hit done and you'll see this doesn't
work it says this table is empty and the reason like i said is because instead of getting a single
value because if you remember it wanted something that says like male or female what we actually get
is this table which has like a column header and the value below from this function get
choice now i'm going to go to that get choice function and we're going to modify that function
so it only returns that single value so i'm going to click on this get choice and we're
going to go here on top under advanced editor for the function this time i'm going to hit ok
now what we're going to do here we're going to add another step in this code of this function
so so far what we're doing this last step we're getting this table results in here
in this value or variable they have here so what we're going to do we're going to add another step
i'm going to add a comma because each one of these steps for some reason they decided to
choose a comma as a separator what i'm going to do i'm going to create a new variable where we're
going to store new results i'm just going to call it res for results and i'm going to make it equal
too so i'm going to take this table object in excel and here there is gonna be this option
first value now if you're in a newer version of power query this autocomplete works if you're in
an older version you may need to just type this in because you might not have this autocomplete
so i'm going to take first value so what this is doing it's going to get us the first value from
that table and the table we need to pass is this table right here whatever this name is in the line
above before the equal sign so i'm going to just copy that paste it right between those codes over
here and finally we want to make sure that when we get this value we return just that value so
this in instead of using this variable name they have we're going to replace it with our new res
variable that we've created here which is going to get us basically the value so at this point
i'm going to hit done and that should modify the function so let's go back to customers and see
what happens so as you can see now we get mail only because our function now actually works and
returns the right information so at this point i'm done with this we did modify everything we had too
so we're going to open this close and load and we're going to close on load 2
and i'm only going to create a connection so the reason for that is none of the newer things we did
needs to be displayed in the spreadsheet whatever that needs to be displayed is already here
so i'm just going to only create a connection press ok and if you look here now we just get mail
in this list so that's now based on this value if i go and update this to female
and we go here and refresh this customers thing see it's going to get us all female values if we
do some random thing and we reload there's nothing because nothing matches this criteria so i'll just
return this reload and now we have all the mail clients just like that so finally if you wanted
this to be more user friendly maybe you want a button here that people can basically click on and
get the values and here you can also make a drop down to have like male and female options here
you could do that too so to do something like that let's start with a drop down here i'm gonna click
in the cell go to data here on top go to data validation data validation and we're gonna do a
list and right here as a source i'm just gonna do male comma female no spaces between these options
and make sure you type that correctly you can also do this validation from a source
sheet if you wanted to i'm gonna leave that up to you it's just a drop-down list validation
so there it is now i'm gonna have two options here male and female and finally i want the button
here that does this reloading because if somebody doesn't know how to do this we might want to have
like a nicer button for them over here and to do that i'm just going to do a macro so we're going
to go to developer tab here and if you don't have this you can always activate it under file options
and if you go to customize ribbon see there's this checkbox for developer now for me that's already
active i don't have to do that so we're going to go to this developer tab and we're going to
click record macro and we're going to name this something so i'm going to call this reload data
again no spaces or special characters here i want to make sure i save this in this current workbook
so if this is under like personal or something make sure you switch to this workbook press ok
and all i'm gonna do i'm just gonna click on this refresh button so that's gonna record that action
of refreshing so we'll just stop this recording and now we should have a macro under this macros
to reload data so at this point i can just go under insert add a button here see this first one
and we'll click on that macro i've just recorded to reload the data press ok i'm going to right
click and edit text and we'll just call this pool data or something so i'm going to right click on
this if you want to move this or something make sure you right click otherwise left
click is going to run the macro we can resize this i don't know whatever you have to do
so finally now i should be able to close this thing and if we go here and choose female click on
pull data see that does that mail that does that if you want to remove this filter from this gender
column you can go under data remove the filter out of there but this should work just fine and there
it is now we're pulling the data from the other spreadsheet based on our value here in the sheet
you can also delete this other worksheet if you don't need it and finally since we have
that recorded macro in this particular worksheet when you go to save this file
make sure you save it as macro enabled workbook
to make sure that button for pulling the refresh works if you don't want this to be
macro enabled workbook then you probably don't want that button so just go in here and call it
pull data from other file thanks for watching please subscribe and i'll see the next video