Extract Data As per Creteria with power Query

Key Takeaways

and these many days i was busy with my schedule that is i'm learning power query powered by word power bi and tableau for that reason i'm unable to take any sort of sessions and here uh i like to share a query and this data is a dummy data it's not an original data it's a dummy data so i like to show you two things to extract a gender as per the criteria that is male if i enter female it should automatically change only the female names with two methods one method is array formula and another method is with power query these two methods i'm going to explain you okay so if you have any doubts please let me know in the description sorry in the comment box okay in description i am going to share my telegram channel link if anybody wants to join just click over there and directly you will go to telegram and you can join directly okay and uh there is a captcha or something like uh some security after joining you have to click then only you can able to join otherwise you are it's not possible to join it it is having only two to three minutes time within two to three minutes time you have to click otherwise it will not accept you will be kicked off from the group now here first of all i'll show you with the formula okay actually you have to extract only the names which is having the gender mail okay here i will show you okay simply i'm i'd like to show you now two methods i said two methods okay this is the first method first of all we have to check how many males are there because there are so many duplicates out there so we have to check how many are there so by using if i will check how many are these f4 equal to male okay i'm also feeling this one and if it is true see this is the logical test f9 wherever it finds the mail it will pick up the true and remaining are all falses now i want one two three this is array constrain so i like to pick up like this so what it will give it will give from 3 to 10 sorry 3 to 9 okay so i want from one two so what i'll do row of d3 f4 now check whether so it will give 0 to 8 by pressing f9 0 to 6 sorry 0 to 6 plus one f9 sorry here f9 so totally seven records are there okay total seven records are there one two three four five six seven okay so it will pick up the 70 code okay this is the first thing and i don't need the faults okay i'm closing that one also right if i press f9 c wherever you find the true it will see what i'll do i'll explain you logical test first of all what i'll do i presented just logical test f9 and values through f9 so what it will do it will pick up the positions wherever it finds the true here true here true here true two two remaining are all falses right so what i'll do i like to pick up the smallest one first smallest one first small and if i press one it will pick up the first to smallest if i press two it will pick up the second smallest right so what i'll do instead of doing like that or what i'll do i'll use the rows function where it i lock this row and i will not lock the this one so it will pick up the first to see first of all i will show you how it will pick up rows of the formula in j3 j dollar three colon j3 okay so it will pick up like this if i show you this one what will do it will check all those things so like that wise it will do like this this one sorry i'll delete this one and now what i'll do just i want the names right i want the names this is very important see guys this is very very important because i am showing the power query also okay that what is the index index part array is the names we need f4 comma ctrl shift and enter wherever you find the name see three main four male five male six male see if i change male to female so automatically change one two seven right and if you want to read out these errors what you will do just go to here if errors if you find any error in this function show me the blank as run this formula as it is right this is one two seven okay now i will show you another method with the power query so before going to the power query you have to convert these two data into a table format by using ctrl t and don't forget that the header should be in the bold otherwise it will consider it's a part of a data but not the header so you have to convert the headers in into a bold before converting into a table so i'll press ctrl t yes here my table has headers okay this is converted and here also control t enters so it also converted so i will name this table like a data data enters and here as table two name is gender enter okay table one table two these two things are there now i'll take some time to go to the power query so please wait for a while the shortcut key is alt a p t all right here all alt plus a plus b plus t okay this is a shortcut key too uh go to the power query and the cursor should be in the table right now the shortcut is alt apt it takes some time so please wait and if you have any doubts you can post in the comment box i don't know why the system is very slow today actually very fast so please be patient it has to convert here you can see loading and right here my mother are also having some problem for that reason sorry for that guys see it's taking much time actually this is a problem with my excel but it will go to power query very fast within span of seconds but uh my action is having some problem i think so for that reason it's not able to go too fast now are you able to see that screen guys are able to screen screen my screen [Music] no only says one screen [Music] on the screen i like to share another screen this one also now are you able to see because the air is sharing only one screen for that reason it's already named with data so what i'll do just i'll go back to the excel close and load two on the create a connection that's it okay just create a connection wait a minute only create a connection add this to data model lower that's it you're not supposed to change any sort of things and another date also we need this one that is a criteria so what you will do just click on wait a minute again you're showing very slow i'm clicking on this okay i said now what is the problem with my mouse you know why there's a problem with my mouse and all those things please sorry for that yes actually there is a problem with my data for that reason and now again i have to convert this into a power query alt a p t shortcut key to convert go to power query that is power query editors i think someone has sent the message but uh sorry uh i can't reply to that message and here simply again close and load already named gender here so close and load home close and load to connection this one also a connection wait a minute it's loading only create a connection add these two data model load wait a minute guys it has to be these two things are only okay it's loaded actually it should be a connection not loaded actually i don't know why my mouse is not working since so many days again how to go i think so actually it is not a loaded it should be a connection only the connection i have not checked properly i think so i have to cut it before going to that see i don't know why it's taking much time to load it's sample small data now but that's not a big date also small data then also it has given some problem let's try with this only like this only let's try it data new query combine query merge let's check it out with this like this actually both things should be a connection but i will load it directly let's see it will work or not directly i'm taking a new query to merge those things see here it is so you need the this one you have to match the gender okay you have to pick up this data first of all you have to pick up the data and click on the gender here also the same thing here you have to pick up the gender and click on this okay so it will give you the reply the collection matches of three of seven rows from the first table so three matches are there one two and three three seven two one okay let's okay if you have any problem please ask me in the comment box so here it is sorry now here is a very important thing you have to do it and if you have any doubts again and again i'm repeating if you have any doubts ask me in the comment box i don't need this table first of all first of all click on this nothing is there that thing is there see i remove the errors i removed the errors this thing only because i already loaded for that today it is showing like this actually it should not show like this okay so i remove this one this one is working over with this remove now close and load two now i will place some adultery automatically it will take the new sweet i think so it will take already a new sheet see all females are there one second all females are there i have to zoom it all females are the name one name two name seven name one name two and name seven and if i change female to male but instantly it will change because it's a formula always a dynamic for that reason instantly it will change but not the power query now what you have to do to change the power query just go to see it's not changed yet see go to data and press on refresh all now it will refresh see here what will happen by one two one two three four four electric force actually i have to say only reflect some uh mud query but i replaced all so it takes some time to refresh some problem with my excel i don't know why it is happening to me for the opinion is taking much time to refresh and until then we'll go to try to go to your control page up click done what happened it has to change actually wait a minute it has to change actually i don't know what happened to here it's showing zero here again i'll do refresh yo you what happened i don't know the procedure is correct but here's some problem with this everything is right so remember corresponding refresh it has to come this one also some problem with this i don't know some some problem with this but the procedure is correct guys the procedure is correct but i don't know some problem with my excel okay if anybody knows how to uh justify this problem please let me know and this procedure is correct only thank you guys thanks for your support have a nice day