10 Ways to save time & Automate tasks with Power Query - Masterclass

hello everyone i'll start off with the simple question let me know if you can hear me all right on the stream i just literally walked into the room i'm still a bit disoriented but i hope uh the audio is loud and clear i am really excited for our power query stream here i could see that we have people tuning in now from all over the world um some of the usuals like philip west from southern california hello phillip how are you uh and eric from netherlands uh hey eric how are you and uh and this is uh excel works from zambia hello excel works welcome to our stream um good morning good afternoon good evening whatever time it is for you i just uh it's about four a.m in the morning in new zealand it's a little bit cold today actually and if you i don't think you can actually hear the raindrops falling but it has been raining all night so uh there is no thunder probably not under today so it will be quite an uneventful stream um and let's let's just read some of these messages uh kaushik from india hi uh hi kaushik how are you um this is uh salathini gonzalez i hope that i pronounced it right uh from canada hey how are you um and and matt ferguson as well uh another usual in the stream in the inspirational and meditation center says loud and clear well thank you so much yogi says hello hi you guys how are you um and uh and chris also joins in uh hi chandu hi my friend nice to see again chris from south africa hey chris uh nice to meet you again here uh on on the stream as well and this is dr srinivasan hi from texas hello dr steven wilson all right um we we are gonna jump into a small presentation just like the usual what i will do is i will set the agenda for us today and then we will jump into to start off we will jump into excel and then i'll show you how to activate power query within excel and how to get started from there but halfway through the thing i will go in and show you uh how to do the same or some of the other steps in power bi as well so that you will get a sense of where it is how to find your bearings within both excel and power bi um i go i gotta jump into this other window here i will quickly um run a poll as well just to ask you um how well do you know power query this will kind of get a sense of where our audience is at i i have a feeling that most people are beginners but i just thought you know this is a good um icebreaker activity for for our stream so i'll post this question now oh well do you know pq immediate and all right that poll should have gone live on the on the stream and and yeah if you check your live chat comments you should be able to see that um and let's uh flash this welcome message welcome and thanks for joining this power query stream if this is your first time here on my stream big hearty welcome to you and um and a big warm welcome to all of the people who tune in every month or every now and then for the stream uh good to have you here on the stream and if you're watching this on replay again thanks for uh joining me to learn power query i do this every last friday of the month so if this is your first time you're more than welcome to join me for the next month's stream as well every month it will be a fairly advanced topic and we kind of go quite in depth i show quite a lot of demonstrations and then take up as many questions as possible usually this goes between 90 to 1 1 hour 20 like two hours basically so all right um what i want to do is i'll jump into the presentation here and then i'll switch back to the the poll uh just to see um as as i could get a sense here we got about 62 percent of beginners which which is really where i'm aiming to show the content but i hope by the end of this session you will um you will you will not be a beginner of power query anymore you will feel comfortable doing some of the data tasks and automations and some of the you know boring things that you do in your work you should be able to take them and do them even if you if if you don't feel immediately comfortable you will not be scared or you will not feel like i don't know anything about power query when it comes to it at the end of this session okay um [Music] and uh we are already getting some questions uh like these what i recommend is um you you wait until we start the stream and and look at the sample data and everything before posting actual excel or power query questions because that will help me address those questions in the context of what is being covered we got someone from wellington this is awesome d uh kiara from wellington good to have you here the um and this is ramu he says your voice is low i guess you need to speak closer to mike um let me know if others find my voice as low as well i thought this is fine i'm gonna quickly double check my mouse my mic settings here nope it's the usual setting so unless i'm just gonna move this a little bit closer just to be safe but it's a fairly good one okay others say audio is good that is uh probably uh you uh having some sort of headphones or something all right um let's just see here um okay i'm gonna close that window now and let's just okay so this is my presentation um [Music] power query 10 ways to save time um and let's start with a silly joke i saw this on twitter the other day and i thought oh this is super funny i need to put this as my starting slides who wants to clean data well as it stands 375 of you do that's how many people are watching the stream by the way and then the next frame is who wants to clean data nobody this is pretty much the reality of uh our our data analysis world isn't it right we all want clean data we all want to be able to do awesome things with the data but we don't want to clean the data because it's a boring repetitive task that that just gets annoying and gets complicated with every new system and new data source and new type of thing that we are trying to build so this is where i think power query really shines it is one of those powerful additions to both excel and power bi which lets you handle data related tasks in a more elegant manner so what we will do is i got like five bullet points that that are the agenda for this what is power query i'll demonstrate that when we get into that and then i'll show you 10 cleanup examples the way i think of power query is it's like a really efficient washing machine for all your dirty data or dirty laundry that is coming from other systems it takes up everything it will load it up it will properly clean it and it will give you nice outputs for you to do whatever else you want to do next so and the third one is i'll also talk a little bit about how once you have set things up how do you manage the refresh how do you update if things change and what goes in that process and then um i'll also talk about how to learn more because the idea of this stream is to give you solid real life examples so that it will set you on a path of using power query for real life situations but chances are whatever you will do in your work that is not something that i have anticipated in this stream i would have i prepared examples based on my own way of teaching power query so it will not cover everything that you want to do so i'll talk about how to learn more what resources that i recommend for power query and finally i will also take up your questions so any questions that you have i'm going to address them i got excel with me in this room because she was kind of she just caught up but she's now gone under the computer go to your bed stay there so those are uh the five things before we begin just the usual stuff there are three things that i i want you to be aware of there's download files for you how to handle the questions and super chat so blank data files are already uploaded please check the video description all you gotta do is on if you're watching it on youtube just uh beneath the video there would be like a video description box expand that and you will see the file details if you have joined the stream uh more than 15 minutes ago then you may not actually have them because you might have still have the stale copy of the description you will have to refresh the page or something i will also i have also uploaded the completed workbook version 1.0 it's not fully complete but it's kind of has most of the power query things that i really want to build um but at the end of the stream i will upload another file uh not immediately but after maybe uh an hour or two so there will be files so go and get them if you would like to follow along you don't need to follow along you can just switch sit back enjoy your beverage and or snack and watch me show you these things and take some notes mentally or on a paper or on phone and then later on look at the files if you have questions more than welcome to ask please start your questions with a queue so that i can spot them when i'm scrolling through the comments on on the stream and please stay on the topic the topic for today is power query so i would love to address your questions but as long as they are on power query they will be helpful for our stream if you have a generic excel question power bi question or any other questions you are more than welcome to post it i may or may not address it but it will help me because once the stream ends i download all the chat comments so that i can read them and then i'll use some of that for future material and if you have posted a question i have not addressed it please do not repeat it is quite rude and it will clog up the chat window for the rest of us and it would be awesome if you can support the stream and my work through a super chat or a super message it is really simple on the chat live chat window there is a dollar button or whatever button that looks like and just click on it and send me a super chat or a super message sticker if you are enjoying the stream all right this brings us to the last topic which is i got a surprise for you well two surprises really but uh we'll talk about the first surprise on the slides the surprise is i'm doing a stream giveaway this time and i'm giving away not one not two but three of these books collect combine and transform data using power query in excel and power bi i will be giving away digital download codes for three of our stream viewers all you gotta do is post a comment just comment anything funny insightful it doesn't even have to be funny or insightful but leave a comment on the stream and i'll pick three winners randomly at the end of the stream i'll try to do it during the stream but if that's not possible or if it gets too much because i don't know why then then i'll do it after at the end of the stream once the stream is done i'll go and download and i'll do some random thing and then i'll announce the winners in the chat window down the video but we are doing a giveaway so hang around in the stream and it will be awesome to give away these books these three books are sponsored by gil ravi himself the person who wrote these books i emailed gil uh at the on monday and i asked i told him you know hey i'm doing this live stream uh and it would be awesome to you know give some of your books have you got any download quotes and kill said oh here you go these three quotes are there for you so it's it's really awesome that he chose to do that and if you have never really read one of the power query books this is the book that i recommend it is pretty good i also talk about other book recommendations at the end of the presentation all right that is it for the slides we will go into these rest of the slides at the end of the session for now i'm just gonna open my data file and then i'll show my face for a minute as i said some of you may not have met excel oops i was locked so i just want to introduce excel to you she's ah yeah oh this is super annoying there she is that is our dog xl by the way and that really messed up all my audio and camera framing uh but um this is alejandro v from misola montana usa he sends an a super sticker i think thank you so much alejandro for and sorry salim says could we remove the banner please it's covering the screen i think it is uh removed now yes there you go so there's quite a few comments here i just uh um tanmay says commenting just for the book always spot the track on my uh we'll see how it goes at the end of the stream jim says i appreciate getting a digital download of the books thanks uh uh well we'll see uh how the giveaway goes in future please make your excel files xlx format not macro enable excel this is a comment from ron i thought these are xlsx files that i have uploaded on the page i always put xlx files xlsx files not the old format this is ashish manon says my first time here on the stream just enrolled into excel 2.0 with dashboards looking forward to it that's one of my online classes that i run um thank you ashish good to have you here and thanks for joining the classes as well um all right um just downloaded it is staffdata.xlsx.excel oh well i'm really sorry for that i think that was not intentional but probably okay um if it is reading as xls it is still just excel file but uh hopefully that's not something that i was deliberately trying to do uh matthew arges also said super sticker thanks matthew and um obedience says i always wondered how long it will take a parcel to go from new zealand to zambia let's try well it should probably just take as long as an email takes time because it's a digital download i'm not going to ship any physical books from new zealand and uh this is from tirumala krishnamura you hope your excel qt dog follows your power ba to be a powerful being for you and we got a few more super stickers i'll just quickly shout out them karla murli and then sudha soma sundaran as well as ron vee thanks have followed you since beginning and then dr sreenivasan as well uh big thanks to all of our or our supporters and everybody else who joined me on the stream all right let's jump into excel here so that i can talk about power query i'll switch between chat and and doing the stream uh to keep it entertaining i may be a bit distracted from time to time because for whatever reason excel decided to not sleep and she's now just going around the house the office room and you know poking her nose into the bin and all the cables and everything so if i suddenly go offline it could be because she started chewing the cable but let's just hope it's not going to happen this is uh the last one that i'll read valentina i don't know if this counts as a comment you remind me of a cricketer hopefully not a lousy one all right uh let's just um jump into excel this is the sample file that i have uh provided you with it is just made up data of a our awesome chocolates company what we have here is some of the employee data and obviously it looks to be very clean just as most data sets that we get when we when we try to work but there are some problems you can actually visually spot them i'll just uh zoom up my screen so you can you can read some of this information the columns all look good but there is quite a few blank values supposedly because we have not coded the other gender or whatever and some employees have this within brackets count information it is supposed to be that if you are not a permanent employee or a contractor your name will appear like this in the system again for the system it works but when you are trying to analyze and do something on the names or whatever this is fairly annoying likewise we got some dates joined there is no termination date supposedly because these people are still here um and this this is these are some of the problems with the data there's more data problems that that we will introduce later on but for now these are the problems so there could be many ways in which you can clean this data you can manually clean it you can do find replace you can write formulas you can do something else or you can go back to the person who emailed it to you or sent it to you and then kind of have a a scolding match with them you know just really ask them to send it to you in a proper format and all of that but let's just say this is what you end up with and we need to analyze this data so the first step naturally is to clean the data and that is where power query comes in power query comes in different flavors uh you have power query within excel because excel is one of the most used data analysis tools so power query is available within excel in modern excel it is already there in slightly older versions of excel you may have to enable it i'm going to use xl365 here and it is appearing in the data ribbon uh in an area on the left hand side called get and transform data so this is the big area here i got a fancy little add-on this time um to kind of zoom my screen i think shift so we'll have to do it like this and then so that's that's where it is i'll have to get a good hang of that but this is my power query buttons okay and but per query as a software and all the features that i'm showing you everything will work as is or slightly better or slightly different but pretty much the methodology and everything is exactly replica within power bi as well so whatever you are learning here today you can apply in two places both in excel and power ba this is why i feel like power query is this one application that every data analyst should at least try and learn because once you learn it you can use it in two places and it becomes a good data skill to have because any analysis that we do they all must have quality data for us to begin with and most of the time we end up with data like this so this is where i think learning some of those data processing skills is necessary sometimes you do it with sql sometimes you do it python and other tools but power query gives you more native natural spreadsheet like feel when it comes to managing data and it is quite scalable as well so okay enough intro with that let's just see how this works what i will do is uh in the example completed file i've done it slightly differently but i'll do it this way because that is more natural so i will i will close this file and then i will open a brand new excel file and then we will try to fetch the cleaner version of data into this file by connecting to that other file where the dirty data is because this way you you have a bit more control on how things are and you don't get to have two copies of the data so i'll make a blank workbook uh this is the time when i fully turn off my face but i just want to have a scan for and just give a shout out to ian and um and then andreas as well uh for their stickers and let's just go here so this is excel as i mentioned uh early on in the stream i'm going to show 10 examples but uh the first four or five i will show in excel will take some questions and then if possible i'll switch to power query power bi to show the other examples but if if it gets too late and you know i've been talking for like let's say nine hours then we'll just finish it in excel so we go to data and this get data is the area where you will find all the things so we'll say get data from file my data is an excel workbook but you could literally do this process what i'm showing for anything you can use your sql server database you could use your azure data explorer or you could use an xml file a web page a pdf file pdf or an entire folder there's ways to do this there are some more examples on my channel as well as on my website so feel free to check those out but for now we will use good old trusty excel file format so from workbook and we'll just have to point to the location where my files are um so this is my staff data file this is if those of you who are following along you may want to try this at least connect you don't have to replicate all the steps but once in a while if you do you won't feel super lost so we will say import now some of you may have done like text to columns and and few other ways of importing data in older versions of excel what he what is happening here is the at the starting point the experience might be similar but later on it gets more and more uh different and it gets more and more automated and you know you will see the power of the power query very quickly so this will show a navigator screen this is usually the entry points within power query where when you want to connect to a source it will give you a navigator saying within the source file that you want me to connect i got all of these things so it found a worksheet and it kind of located the data that we try to get so this is the data we do know that this data is not good enough there is some problems with it so we will not load it load means i i've got what i want just get it to mean in this excel file whereas transform means that i haven't really got what i want i want to change some of the things with this data so we'll click on the transform data and this is when ladies and gentlemen we enter the world of power query so you see what you're seeing here on the screen is nothing but power query um and this this particular screen window here is called power query editor you could think of if you have done any vba programming within excel you are seeing the excel spreadsheet but if you go to visual basic editor you are then editing the visual basic code that is responsible for some of the things on the spreadsheet same with what we are doing here we we get into this behind the scenes screen that will talk more about your data and let you change things about it so we got our power query editor and it kind of looks like an excel spreadsheet but it is a special type of screen so we will understand a little more as we make progress but given the fact that most of you may be seeing this for the first time or the first few times in your life i'll kind of make some simple comparisons as i said it looks like excel so we'll treat it like excel there is a ribbon on the top each ribbon will do certain activities so we got home transform add column and view uh if this is your first time using power query what i recommend is again this is something that is optional but i highly recommend doing this go to view and enable formula bar okay especially if you plan to learn more power query then doing this will be very helpful this is a one time thing you do it once it sticks so next time when you open power query it will automatically show the formula bar across all of your excel files we'll go back here again the actual screen the way it is laid out is more or less similar but certain things will be different depending on which version of excel you are using and how old it is and all of that but in the newer versions of power query they have added this nice little green bar on the top this is called a data profiling bar it will tell you highly at high level the quality of the data that power query found in that file so for example gender column is not all the way green and when i put my mouse pointer on it it tells me that uh only 96 percent of the values are full four percent are empty there is termination date here nine percent are full and 91 percent are empty so this will give you like that kind of a sense of quality of the data if there are some errors again it will appear in a different color but that's that i'm just gonna quickly pop into the chat window here to see what is happening there's some quite a few comments there probably because there's a giveaway and i'm really sorry if i'm not able to read and respond to all of them so ron asks this question in the source name i renamed the sheet to data in navigator it does not show that sheet name so it is possible that power query is referring to a cached version of the file you may have to save that file close it before and then come back here then it would it should read that as as data this is vignesh hey chandu from chennai i'm here to get some add-on knowledge on power query literally even sacrificing my time with the time with my girlfriend by the way this is not for the book well uh [Music] yeah this uh i think uh i'll respond to that by saying not all heroes wear cape or whatever uh high pressure all these learnings are very much useful in our real-time reports please explain more about unpivot option thanks advance uh okay we will we'll do more of that later on can you please zoom a little bit uh this is from susan can you please zoom up in a bit your screen is not clear thank you i will try the this is the most annoying thing about power query um there is nothing to zoom here like in excel i can zoom the screen here there is nothing this is why i i told you guys that i got this fancy thing but the fancy thing is it will only expand the point that i'm pointing to but if i move my cursor there you can see my cuts are moving there the zoom is not following me so i'll have to investigate a little more on how to get some of that done but i'll try to do the zooming thing every now and then if i'm doing something critical all we did is load the file so uh really not not that much and this is uh and he's saying kindly close the chat box you can disable the live chats on your on your stream view within youtube there's nothing that i can do uh the whole point of doing a live stream instead of a recorded video is so that i can interact with you and and talk to you so i prefer to have the chat window uh and do it barbara sense in a super sticker as well thanks baba and um and this is from bren see he says or they say i'm not able to see the column information what am i missing as i said the column profiling is something that is added recently into power query i don't know how recently it has been a while now but if you are using like excel 2013 2016 or xl365 but like a few versions behind then you may not have that feature i suggest looking into the view and see if you need to enable some of these things i'm not enabled any of them so the green bar was something that is there but i could be wrong okay we will go back here some people are saying uh the screen not clear kind of things it is not something that i am i'm controlling like there's no button here that i'm just cranking up or down to reduce the quality or anything it is just there's a lot of variables probably your bandwidth and how busy youtube is and if there is like a live music concert happening then probably my thing will be slightly push it down or whatever anyhow we got the data here we got we have a job we need to clean but before we clean let's quickly understand some of the areas on the screen the top area is called ribbon this whole window is called power query editor and it will have broadly speaking three big areas within this you have your queries panel on the right left hand side this will list all the queries that you have built right now there is only one query so it is called sheet1 and that's that is what it shows here and we'll go all the way here this is these are the query settings so i can customize some of the query related aspects here and that is the preview of the query you might think hey what is this query that you keep talking about we are looking at data here why don't we call it as table well within power query the whole process is around queries it is another way of thinking table of data but there's more technical differences there what is really happening here is we are building a query the way this query works is um don't worry i didn't go anywhere i'm just getting my sketch pens so that i could make a drawing for you we'll do one of these things now we'll make a drawing so we got our excel file here that excel file and it is dirty not not clean what we need is not this what we need is a clean cut of data that's what we are looking for so the process of going from here to there you could think of that as uh query okay again if you look at the dictionary definition of the word query query is basically asking but the in within the world of data analytics and database you can also synonymously use query for taking something changing its shape and size and nature to get to another state so this is what queries hence the name power query you may have already heard some of the other querying things sql is another type of querying thing structured query language is also used to take something turn it into something else that something can be one thing it can be many things like it could be two different tables and you want to combine them get one final table you know all sorts of things and then along the way the microsoft product manager had this crazy idea wait a second query sounds too complicated let's make it simplified so then they decided we're gonna rebrand this as get and transform data okay now i don't know about you but this is quite a mouthful to say and it doesn't really do any better job of explaining that than this i mean now given that that is a technical term but anytime you see get and transform data you'll be like what is this so this is the reason why within excel sometimes power query is the the ribbon areas are all called get and transform data and then you go into the query editor it is it also says power query editor so no one knows what it's going to be called next year so we'll we'll just keep calling it power query so the process that is happening all of within this is nothing but querying but within the world of power query because you're not really taking one and somehow magically turning it together you need to actually go through six seven steps so there will be some steps applied at at each stage so we start with the excel file we then do one thing we then do another thing we then do one more thing so that's the steps so now we'll go back to the screen and then you can connect these dots with with what is happening on the screen so here you can see that these are these are my queries these are the query steps so within this query even though we thought we just loaded the data we haven't really deliberately done anything it has somehow done four steps already okay you can go and kind of read the steps if you click on an individual step it will show you how the data looked at that point in time so here at the source step it is how this is how it is looking then within the navigator we told excel that for query that i want to go to this sheet so it went there and then it recognized that there is a row of headers in the first row so it automatically promoted that and and then applied some data type changes so this is where we ended up with right now we got four steps done and the data is looking like that these are the steps we'll start off with the very very simple thing we'll name this query as staff data so you can type any name press enter you'll see that the query name changes and it kind of reflects here as well awesome right now the very first thing that we want to do is fix this gender problem the gender column has some blank values and when it comes to doing any kind of distributions or analysis of how many people we have by each gender within the department or whatever it gets complicated if there is blank values there so let's uh there could be any number of reasons why these are blank but i'll i'll give one reason because hey i made the data so i make the rules so the reason why these gender things are blank is because the legacy system where we are using to keep our employee data is not capable of storing the non-binary gender so that's why it is coming as blank so wherever it is blank we would like to simply put that as other okay so we want to add a data processing step which simply says if the gender column is blank then just put the word other there so right click there could be any number of ways of doing this but essentially we are looking for the words null and replacing them with other if it is blank power query usually says that as a null so we select that column and then from there are multiple places where you can find it so if you go to transform you will see that there is a replace values option it's basically like find replace so we will add that as a replace value what do we want to replace we want to find null this needs to be in small letters and then replace with other okay let's try this here no it's gone so we want to null with other and then when you click ok power query will add a data processing rule this is called replaced value and it will replace you might think this is quite manual what are we talking about automating here what we did is just manually well it's not manual we are teaching power query that whenever i import data from that excel file if you find a blank in the gender column just make it other so right now with the current data set it is working but in future if you get a new file with let's say 600 employees you you run the import process again it will automatically do the other step for all of those values so that is the beauty of this we now added another step so every time you add a step it will appear in this applied steps bin okay and if you make a change of your mind you like oh other is not good enough we need it to be something else you can change the step to change the step locate the step which you want to change and there should be usually like a gear or a cog symbol next to it if you click on it it will open the screen again for you to make any adjustments i'm not going to change this well i just thought i'll show that to you so this is our number one first transformation that we have done within power query okay at this point you may be tempted to go and test it out but i'll do a few more things and then we'll test it out once and then we'll come back for more i'll also read some of your questions at that stage so the next thing that i want to do is i want to take this name column and fix this contractor mess you can see that certain names have contractor next to them within brackets and it's not even the full word contractor it's a ceo nt and if you are looking this file in your computer while watching the stream you will notice that it's not as simple some people have their name space and then bracket contractor other people have their name no space just brackets contractor so the it's basically more messed up than it looks on the screen so what we want to do is we want to first figure out if it is just a name or it also has this word context to it fortunately it will always be just c-o-n-t but that's the that's how it is formatted so we'll select this name column and we will split it we will say split it if you find an open bracket okay open parenthesis so right click find the split column option within power query there are many way places where you can do same operation so usual place for me to look for things that i want to do are right click and see if that is available there if it is not available there then i start poking in the ribbon so usually within the transform ribbon is where i will find things you might think i see the same buttons within add column and transform how are they different we will understand that at a little point later in the stream when i show you how to add column and how that is different from transform but for now we will say right click on the name split column you can split in any number of ways i'll split by delimiter okay you can see that there are some other interesting ways to split for example non-digit to digit this is awesome for you if you have data where both text and numbers are mixed up and you want to separate the text with numbers excellent way to do that but we will use the delimiter option and the delimiter needs to be it's not space it's not one of these so i'll say custom open bracket and when you are splitting you can also tell power query how you want your splits to be done you want it to be once at the left most or right most or each occurrence of delimiter so these are excellent options for you depending on how messed up your data is sometimes you get like five people names in one cell then you would want this each occurrence of delimiter option whereas other times like this i don't want this to split it into 16 columns even by accident i just wanted to do it once at the left most because i know the the left most the first parentheses is where the employee type would be so we'll do it like that and okay so this will add one more step actually it added two steps but it will give you two name columns name one and name two okay let's try this again uh so you can see that is name one there is another name too as well i'm just gonna quickly pop over to the chat window to see what is going on there so there's a nice tip from mpp ms zoom it up utility app may help with screen sharing zooming i'll definitely try that out next time man thank you so much um and this is a question from prebling why do we need to write null for replacement with other you can try by not writing anything as well and if it works that works but i found that just writing null makes it more explicit for you when you write it in small letters power query will treat it as null and it will work bastizoo says another great session thanks shandu thank you boss that's good to hear i hope everybody else is enjoying as well i am having fun and finally excel countdown so uh i won't shout but i'll keep my usual volume and this is a question from anguraj why didn't the open bracket come after the split this is because that's how the delimiter the word delimiter means it's it's actually a separator so it was looking for that separator as soon as it found it split so that is the splitting point so it will not appear in either left hand side or right hand side it will be gone if you if you want to keep it then you would need to use another technique and this is craig uh he says i find automatic type detection creates more problems than it solves it almost always delete that step when it happens and then manually set types later awesome comment correct this is pretty much how i like my types as well but i i find that especially for the early stages when people are learning power query it's easier to just leave them on rather than worry about what's going on on all points of the screen i i remember reading somewhere in in park bi updates blog post that they're now giving it as a choice rather than a default so hopefully that that happens but if you don't know what this means or if you're like oh what is these two guys talking about then just ignore that comment for now but yeah we will talk more about that later on so massey edge says this query fixes the source file tab and table can user can you allow user to choose the file and tab himself that is the whole point of the navigator screen when the navigator screen appears you are picking a choice like i want this and whatever you pick then all of the steps will happen from there on but if you want a bit more flexibility like uh sometimes it will be in sheet one sometimes it will be in sheet two or sometimes it will be sheet three then you will have to get a bit more creative with the way your query is built uh you could of course do it there are ways to it but i'll leave that as a fairly advanced exercise for now okay so we got our name one and name two name one looks all right it is proper name name two is where the problems begin before we fix the name two let's go to name one and we do know that certain people had this extra space before the open bracket can't begin right so there's an extra space somewhere we don't know where the space is because we can't see the space right so how do we remove that extra space this sort of an operation is called trimming again a technical word but maybe you already know the word trim from excel trim formula so right click on the name one will say transform trim now some of you might be thinking how the heck would i know where to find this word trim i mean i have been using power query for a while so i know where to find it but if you have just started using you wouldn't really have a clue where to find it so one way is just keep poking your nose all around the place until you find it but another option is you can also use the ribbon on the top and from there um this is super funny actually from there it is not in anything but it is actually in the format option so it's not in something else it's in the format so but wherever you find it you you make a mental note of this is where it is and next time you will just go there so we'll trim it this will remove any extra spaces at either ends of the text so if there is a space in the beginning or at the end it will be gone and bathroom sensor is super sticker as well thank you birth so that's that fixes the name one uh it is called name dot one we will fix the actual column name later on we will try to do the column renaming kind of stuff at the end of the query not in the middle of the query that will be easier and then where it says name to this is my employee type so if it is null that means you are a permanent employee if it is can't open bracket then you are a um you are a contractor so what we will do is we will use this name to value to introduce an employee type column that will kind of determine what sort of an employee you are so this means now we want to add a column okay so we'll go to add a column the column is conditional right it should be permanent if this is null and it should be contractor if your count open bracket so we will click on add column conditional column you can also write the code yourself but it's a lot easier to just use this and this will give you an option what is the column name column name is employee type and it is kind of like an if formula builder basically you just build it yourself and then it will work so if my name dot two equals and we'll just say count open bracket then the output needs to be contractor else it needs to be permanent okay so this is how you can build you can reverse the condition as well you can say name.2 equals null then it is called permanent else contractor you could use any other methods to do this as well and when you click ok we get an employee type column all the way at the end usually added columns will always go to the end and it will appear in a nice and clean format there now that we have added this we no longer need this column here so we'll right click on name two and remove it okay you might think oh whoa what's happening here it's all going super fast well really all we did is we did the three steps so i'll do a quick illustration this is the only time i'll kind of slow down and explain the actual process later on we'll try to make it a little fast just so that you know you're not stuck here with me on this stream until christmas but we got a messed up name so i'll call this as name dirty and there's some names step one we split it with an open bracket delimiter so that's the bracket and we split it once i have split step number two is we ended up with two names so we'll call this as n1 n2 and for n1 we then trimmed it basically remove the space from beginning or end for n2 which is my second part of the name we then use it this little thing to add a column which looks at the end to value and then it decides the output basically if you are null then you are permanent if you are not null then your contractor so p and c so once all of these steps are done we then went and kind of deleted this guy n2 you might think if you delete this how are these two still in place they will also be gone because that's how excel formulas work don't they when you write a formula on a cell to do something like any formula on on the contractor type cell if you delete the original source cell then the formula no longer works that is where the crucial difference between excel and power query emerges within power query it's basically every step uses a copy of the data from the previous step it's kind of a loose definition but yeah so it's not really making a live reference it is basically saying in that step you had that column let me take that and do this and introduce a new column so this new column is introduced based on a copy of the data from the previous step so it's not live data it is just a copy so if this is deleted it this will still work because it is coming from a copy kind of thing again a very loose way of saying the internal thing might be more different and complicated than what i made it sound so this is the beauty of power query that means you can kind of build more complex steps that rely on in internal things and then you can knock off all those individual things and finally get just the cleaner output as the way you want without having to maintain 67 helper columns in your file so another big reason why such cleanup activities are more easy and native to handle within power query rather than using formulas or vba or anything else for that matter i just want to shout out to these people jakoda uh and eben for their uh lovely super stickers big thanks [Music] all right um so we'll go back to the to the thing here i got my name one i got my employee type i got my department date join termination date salary employee id okay so what we will now do is we will then look at this data the purpose why we are doing all of this cleanup is because we have to produce a month-end employee report where we need a clean cut of data but also we need two more cuts of data one is we want to have a an extract of all our recent joinies in this case the recent joinings are people who have joined the organization since let's just say arbitrarily first of july 2021 so i want a recent cut of data that's another cut likewise i want a second cut of data which is just all the people who have left the organization so i got one data but i i for my reporting purposes i need three different types of data one is my recent joinings one is my exits and one is all employees so staff data gives me all employees but i also need to make these two other uh for lack of better word views of data same way same data but represented differently so we want a copy of staff data but it is staff data table looks exactly like that but has fewer rows depending on the conditions so we will create that to create a copy of this we got staff data we'll right click on this and then you can do a couple of different things you can duplicate you can reference so again many people confuse between these two like you know what is the duplicate what is the reference i'll show you we'll duplicate what a duplicate will do is it will duplicate all the steps as well this query is running in parallel uh and it will do all the steps that you have done in the other query so that's what a duplicate is so sometimes you may want to change an in between step to create a different data set that is when you would use a duplicate we don't need to use a duplicate duplicate means we are doing twice that work we already have done all of the work once so in this case what we will do is we will make a reference a reference on the other hand will only have one step it simply says get me a copy of the data as of that data so this is just a reference so this reference query we will rename it as new joins and here we'll simply add a filter condition on date join so date joined click on this it's almost like excel filter date filters and then we'll say in the after and after or equal to and then we'll just type the date so it will give you a filtering of the data and you can see what it is done we'll just do a so it's filtering on that and it will give me it will give me all the people who have joined since 1 of july 2021 i told you early on in the stream that you need to enable the formula bar so i'll show you now how that particular formula looked for filtering this is just for your own amusement there is no real value in learning these formulas at the early stages but as you get more and more sophisticated user of power query you will have to start messing with these formulas as well a little bit so that's where learning that language helps so [Music] i'm gonna just think what is the best way to show this on the screen maybe i'll open the word and paste it there with big font that's too big all right hopefully you can read it here now it says table dot select rows source each date join greater than or equal to hash date 2021 7 to 1. most of it kind of makes sense this is the date that we have set so that's that each day doing the game kind of makes sense why there is a word source and what is table dot select rows as i said the intention of this is to not introduce this language to you power query uses a special language called m uh to kind of program things that you're seeing on the screen and you can learn more about it but the way this programming language loosely works is at each step there is a formula and the formula usually takes the previous step item in this case the previous step is source so it refers to previous step and it will do some operation on it so that's kind of a very loose way of understanding this i won't go into more technical detail for now because it will get more complicated and muddy but if you enable the formula bar at least passively you can learn the language you don't have to learn it but having the formula bar is like you know let's say you suddenly go uproot your life and decide to live in i usually say paris but i'm just going to say hyderabad and you don't know any telugu language which is the language most people there speak but fortunately you got this friend slightly annoying but hey friend who will just literally translate everything that is happening and just show it to you on on on their face all the time so you you're not there to learn telugu language you're there to have fun and enjoy and whatever but every now and then you're watching his face and you learn that oh this means that this means that so that's pretty much what we are doing with this formula bar you have it on there you can see that oh seven one and then the next month you will have to change this to let's say eight one or six one you don't have to go through all the things you can just go here and type six and press enter and you bang it changes so it's that simple this language once you start learning that's why i have told you to enable the formula bar so you can see what's happening for every step and if needed make changes once in a while without having to go through six screens and click at lot of different places okay so this is new joinies we'll add another duplicate sorry another reference this is my exited staff and this is where we will simply say if your termination date is not null that means you've gone and we want a copy of your data so i got my three different queries now notice that this query and that query are kind of dependent on that so when power query does its job first it will finish this and then it will generate these two outputs okay i think that is a very very very good progress on power query front we have made lots of changes to the data we've now created three outputs from one set so let's close and get this data into excel so that we could actually do some analysis so how to get that it's simple when you finish your job here you click on this big save button here close and load there are different ways to load the data for now we'll just click on this and then see what happens it will list all the queries it will load separate tables in separate pages as its as it is doing its job we quickly realized ah dang it we forgot to name this column properly it says name dot one it should be employee name we'll do that later but for now i think this is a very very good start we made some changes to the data we've collected some output now if you have new data in this file so this is my staff data file all i got to do is we'll do that first we'll go here we'll change something from this data okay something very easy so we can all follow along we'll change bar funny salary the very first person from 75 000 to 89 000 and also move them from procurement to sales department all right we'll save this file close it come here notice what happens to this right click and then you can also go here and refresh you can also right click on the queries panel here and refresh pretty much the same thing i'll just refresh this from here instantly it will refresh it will show the updated data it will calculate the permanent employee type all of this information is done with without you doing anything if bar funny is a new employee or an exited staff their data will appear here as well in this case they're neither they've joined all the way back in june of july of 2020 so they'll just appear as it is all right let me just uh quickly have a scan of what is happening in the chat area [Music] we seem to be having some spammers in the chat so chandan rai asks how to make a key based on employee id and date joined um again um i hope others know what the word key means key is basically like a unique identifier in a way and you can make it easily i think somebody else kind of gave you some suggestions i'll talk about this because it's an interesting questions and maybe others may want to know when i switch back to the screen um shikar sign says uh where reply or this stream is are available actually have to rush so just wanted to ensure this will be available so the plan is this will be on the same page where you're watching the youtube video so if possible just like the video so that it will appear in your liked videos and that way you you have a place to go back otherwise if i have emailed you saying no it's starting in some time then you can always click on the link again it will take you back to the reap link when the stream is done yeah so that's that and let's see this ani says for the explanation of deleted column where if condition was applied does it imply that till the column exists the formula will still be dynamic and freeze once the column is deleted it's not really like that while you may want to think about it like that because that will kind of help you mentally understand what's going on the reality is slightly different and it gets a bit more muddier as you start building more and more queries so for that reason a better way to think about it is at each step power query will reshape your data and make a new set of data and from there on it will just use that data so it kind of moves forward uh and at each step it makes a new table and then that table is used further on uh but it it is not as simple as that as well but it is a better mental model than freezing and that kind of thing that you are mentioning simon says can you format the word doc as a landscape so it's easier to read please well we are too late for that now it was already beyond that but uh so add knock backup says can we refresh the query file while the source data file is still opened uh i've never tried that but i think it shouldn't really be a problem because the way windows refresh and all works is i'll show you here this file is open [Music] i'm just gonna denison a contractor okay we added that we just got to save this and we'll see i've not tried it in a while but it does work so you can see that this is a contractor now even though that file is open so that's not really a problem um yeah i think that does that's that on store says what if the staff data next time is dynamic regarding more or less columns columns orders changed and the format in the columns are changed um again possible within power query to dynamically handle most of these kind of uh data mess-ups that happen in real life um this this intention of this stream is to introduce you to the concept and kind of not overwhelm you with all the technical details of what is possible and what is not but what i recommend is stay around until the end of the stream and i talk about how to learn more where to go from here on and hopefully some of your questions like parts of it will be addressed later on in the weed in the stream but you know if you still have questions you you will find those additional resources valuable everything is possible it just takes more steps that reminds me you know i wrote some well i didn't write jokes i i think about when i do every stream every month then i like to put some dad jokes into the stream just to make it a little bit funny and amusing for you as well as a little bit excitement for me i guess so one of the jokes that i wrote is this what is the favorite exercise of a power query developer any guesses well even if you guess you couldn't tell me because i'm the only one talking on this stream it's taking steps i'll stop now all right let's see what this is dr sreenivasan says what happens when the column header is changed so this is related to what the other question is isn't it we'll see what happens sometimes it will work sometimes it won't work this is this will take us back to what our friend earlier said which is power query is adding this automatic data type change steps which can create problems so i'll show you what that problem looks like because i think this is more interesting so we got our file here uh some over enthusiastic data analyst who is preparing this file and sending it to you decided that mpid is a bit too complicated will just delete the space here and call it as mpid okay save this we'll see what happens here refresh we'll get an error here the column m space id of the table wasn't found and when you get an error during the refresh it will not mess up the current table that is loaded to excel it will simply just give this kind of like warning symbol on the query so it's saying further refresh is not possible um neither of these are broken but they can they will be broken once you try to build it so we'll go and fix this problem here we'll go to the edit query so this takes us to the next topic which is we've built something we have refreshed it how do i go back and edit it now there is a problem so we have to go and edit it because we can't go back to that file and change it that file is coming through us from a whatever things system is giving it to you so we can't change the system we have to change it here so you can edit the query in multiple places if you have the queries and connections panel visible you can right click edit but let's just say you closed it away now you can't get it back you can go to the data ribbon click on queries and connection to bring this back and from here you can edit alternatively again this depends on the version of excel you are using but if your table is a result of a query there is a query ribbon as well and from here you can click on the edit there might even be an edit button i can find it there so whatever way you find it possible you go into the edit screen this will take you back to your power query and you see this little red color bar now it's no longer green or or whatever it is red because there is an error the error is happening at this stage of the refresh if i go to source it is green that means things are working here things are working here as well and things were not working here okay so we will see how to fix this problem we'll just say start by refreshing the preview this will kind of refresh the file within here and now it says amp id without a space promoted headers work change a type this is a step that power query adds every now and then because power query is what i call a strongly typed language again a technical term but essentially what it means is power query doesn't like things to be loosely hanging so if there is a column in your table power query wants to know what data type it is it is an annoying thing for it think of it like this you know you may all have like an annoying friend back in school days who is always a couple of steps behind and every now and then catches up to you and then says what's happening what's happening guys what's happening so this is what power query is it really wants to know what the column type is at every point of the journey it doesn't matter what you're doing but every now and then it needs to know what's happening what's happening what's happening so that's the thing and if you don't tell what is happening like you don't tell what these column types are it will take the liberty of deciding on its own so it says oh you know you're not telling me what these columns are i will do it myself bro i'm going to change the data types the big problem of this changing data types is it will up happen as of the first time you build the query the first time you build the query it added this little piece of formula here time for us to put it in the word this time i will i'll do it like this so it's easy for you to read so hopefully you can see this here uh the step is called table.transform column types again we have very long name it's saying hash promoted headers which is the previous step so it's saying take the data in the previous step and then amp space id column should be integer type and 64 dot type is basically integer name is text gender is text department is text age is integer date is date whatever you you see the big problem here this is assuming that the column will be called amp space id but it's no longer called lamp space id and that's why this problem is happening in the first place one easy fix is you can just delete this step this step is useless like what is the point of this so we'll delete that this will not make any assumptions it will just promote the headers whatever the header is it gets promoted so it will go up there it will still split it will but the problem is every time you introduce a new column power query won't do this what is happening guys kind of thing and then it will change the data type again but this time it is uh only doing it for those new columns so it's not a big problem and it works no issues so that's that but it's not gonna be perfect because okay well i said it too soon i think yeah it is not going to be perfect because it wasn't really working for the termination date you see what's happening with the termination date it's not filtering the terminated employees anymore this is because when we are not converting it into any data type power query assumes that it is abc123 i'll show it to you you can see that that's the data type abc123 which simply means it could be anything i don't give i don't care about this anymore i'll just call it as abc123 so the problem with abc123 is certain things like filtering out dates cannot happen because power query is not technically thinking about them as dates so that's why this is happening one way is we could probably change the data type of that to date and now this will make the non dates as null and our exited staff will work properly again if you're feeling a bit overwhelmed with all of this and you're thinking man a simple column name and it is breaking everything about this awesome software well because it is kind of built like a bunch of dominoes right every step relies on the previous step to work properly and in a previous step if i made an assumption that there would be amp space id column then it won't really work anymore so you got to keep all of that in mind but once this is done you you save it then it will refresh your query and it will give you that so that is a very long but hopefully fruitful and interesting answer for for that question okay [Music] ron says don't give up your job for a stand-up comedy oh well uh if if people as many people will watch my stand-up comedy gig says this stream then that should be fine but otherwise i'll be like back to excel i think i'll make excellent for some more time okay let's see rohit asks this question which is can you add a calculated column to the query during the transformation process yes you can that is the next topic that we are going to coincidentally talk so i will show that to you but this is more interesting question as well poor v says if i want to run these steps on another file how do i replicate it this is a very very interesting question and this is one something that i hear pretty much every time i demo power query in a live situation people are like man i love this but i don't want to do this same 16 steps in all my files how do i do it like that so we'll talk about some more of those in a while but for but for now let's just finish reading some of these other questions uh i think ron already gave an answer to you poorly there and then this is from chandra can we get data from pdf uh pdf file um yes you can you i don't want to show all the steps uh here because it's just there's no value in going into a different direction but if you go to my website one of my recent articles is called extract data from pdf to excel step by step tutorial so this is where i show you how to use the from pdf of option of excel power query it goes into quite detail and if you like to watch me do these things there is a video on youtube as well so please feel free to check it out and that will give you more details on that manju says what if for the next report we get new source file if we rename the file as the file we have the source file name how would these steps work on that file so if you rename the new reports file like every month and you are getting an extract from your whatever system keeps this data if you rename it as this file and put it in the same folder location then everything will work just as it is you don't even have to worry about it you refresh power query will go to the file it will get it because the connection is kind of made to that specific file name as long as the file name is kept it will work the challenge becomes more hard when the file names are not kept consistent and you still want to be somehow able to run this that is when you need to think a little bit more on how do i automate that step as well again it is possible i'm not revealing all the clues here but uh you know you can finish learning and then go and think more actively about how to change some of the beginning steps to do that here in parix says can vba macros record all these steps unfortunately not because the vba engine is i don't think it can reach into power query in engine there but power query has a different way of recording the steps i'll show that to you because that'll answer the other question that uh that we we had as well so let's just jump back here for the full screen view i'll show you first how to take this data all the steps and then apply them on another another thing so we will go query edit and from here first up i got my nice query built all of these steps are there and i want to take them and apply them in a different place then i just want to see the actual raw code that is being produced by this so we could go to view and from here you can click on the advanced delta don't be scared by this name it won't bite you when you click it will open up a screen like this where it will give you the power query code use it to go from your source file to this i'll just uh copy this i'm gonna paste it here there's almost no point explaining this code because it's just too complicated uh at this early stages of learning power query but it is in a way kind of like plain english so you could kind of read it like a really boring complicated novel and try to make sense of what's happening we'll see so this is where the code begins it's it always starts with let a special keyword within power query and source is my excel workbook blah blah blah you can see the full path this is where my file is coming so if your path changes this is what thing that you would replace so you can kind of take this copy open a new query paste it in the advanced editor and then adjust this bit alone to map it out to different folder different file and bingo everything else will work as it is so that's uh in a in a very loose nutshell fashion how you would take this and extend it there are more complicated techniques you can even build like a template file and use it and as a best practice and whatnot i'll leave rest of it for your imagination okay so the first thing that we want to achieve is we want to add a column that will do some calculations for the purpose of this we got some salary data so i thought it would be cool if we can add a bonus information for the salary we'll do two scenarios so the first one is fairly simple but then the second one is going to be more interesting but both of them will give you so we got my salary the first one is everybody gets three percent bonus so bonus is three percent of the salary how do we add it that's number one second we will continue this theme we got date joined we would like to calculate the tenure of employee that is how long they have been here in years so day join will remain we will add a new column that will tell me what is their total gap from today to their date joint and that that should update automatically if the if the date changes so tomorrow you open it should refresh it will change the calculation so tenure the third one is we want to calculate bonus based on a mapping table the mapping table will have for each department what is the percentage for each of our four five departments and we want to use that mapping table to calculate the bonus so this is what we are going to do next this is basically now moving away from some of the basic stuff that we are doing into more practical hands-on stuff gets interesting uh it only took us 90 minutes to get there but hey no complaints so three person bonus is very simple you get to salary now we are adding a column so you want to go to the add column okay and there are many things that you can do within the add column i'm not showing my screen sorry we go to the select the salary and then we go to the add column and from here we'll simply say do a standard arithmetic operation of multiplication okay you could do a couple of different things i think you can do a percent off which is more than multiplication so we'll just do the percentage off and then we'll see what that will do so we'll say three percent off so i'm just going to type three there when you click ok that's not wrong i think that is wrong i'm gonna delete that step so to delete the step you would add it you would simply click on that little x mark next to it and that step is gone it's like undoing we'll select the salary i think we need to have percentage yeah this is three yep that works so i'll repeat these steps because it wasn't really smooth so we want to select the salary go to add column standard percentage i think percent off is like a reverse of percentage um and then we'll say three for three percent when you click ok it will calculate three percent of 89 000 and print it here it will name that column as percentage as well and it will have decimal point if needed so that's why the data type is 1.2 and it comes up here now if you want you can change things about it for example we want to round it up we can then keep the percentage column because now we don't want to add a new column we want to change this column itself that is where you will go to the transform ribbon and from here we will say rounding and we will simply round it up to the nearest digit so that will that and then we will rename this column as bonus i'll double click on the column header and then we'll call this as bonus one why am i calling it bonus one because i got my second bonus as well which is using the mapping table so to keep it simple we'll call it as bonus one and i got my bonus column notice that this bonus column is calculated here but it will appear on the new joinings table as well as exited staff table this is because there are references to the original query so whenever this query changes these two will automatically pick up those additional bits if you want to see the bonus right next to salary before the employee type you can click on the bonus and move it in the middle it will rearrange the columns as well likewise let's just say you don't want employee id to be here it should be all the way at the end you can move it there it doesn't matter how things are in the original file but when they when they get saved and sent to excel this is how it will go so you can rearrange columns like that so that's my bonus let's see okay i think everybody seems to be along the same place so that's my bonus one we will calculate the tenure as well the 10-year calculation that i'm gonna show is not going to be super accurate uh this is because some of our employees have left but we will just try it as a start and then you can later on fix it so first thing is we will convert this date join to a date column that way it will be easier for us to do the date operations so right click change type date there are multiple dates here depending on how your data is you can use one of these options we'll just use the date and that will convert that and from here date joined is now there we will add a new column when you select a column depending on the type of the column certain buttons like line up sorry light up on the screen so you can see that if i select the number column this is lighting up but if i select the date column this thing lights up so from the date we will add the age option age is the word that power query uses what it means is age of the date as of today so essentially tenure so we'll add age it will give you the output in this format four zero two zero zero zero zero which is basically how many days minutes hours sorry how many days hours minutes and seconds it has been since that person joined the organization we don't need it in days so we want this to be in let's say total years or whatever you can select this column and go to the transform ribbon from here this is a data type of duration so i can change this to total years again the confusing bit here is when you select columns like this you will have same set of buttons in both add column and transform add column buttons and transform buttons so it will get a little confusing and sometimes you might add a column without realizing that that's what you're doing but you want to just double check your steps every every now and then to make sure that you're on the right track so we'll add total total years transformation and they'll give you total years 1.001 whatever whatever and from here i'll just quickly round it to just one decimal place so that we will get 1.1 and then we will call this as tenure and again another column is added i can just move this in the middle and you can see that that will appear in all these other queries sorry if i'm a little fast there but i'm just trying to because i've written some ideas that i want to show you i just want to cover those things before we get into almost two hour mark there all right so this is my tenure that is my date join i hope that wasn't too confusing and you know if you if you are a bit confused please feel free to download the example workbook and when the stream is done i'll upload it this file as well so you you got files to refer to and you know you will be able to retrace the steps and then see how things change by just clicking and going one step at a time to see what's happening so this is how tenure is added we'll do our third and most exciting thing before that i'll just quickly jump into the chat window to see what is going on there um i just want to first give a big shout out to ron for being there throughout the stream and helping other commenters with their questions and sharing what he knows big thanks ron thanks for you know helping the the viewers and um see this gilbert says i guess renaming the steps will be helpful in the future to know exactly where we did each change yes it is useful ma what um because power query kind of i mean we when we started we only had like two or three steps but right now we got close to what 15 20 steps so it gets kind of really big very fast the steps keep adding and when you when you are building more complex transformations chances are your steps could be hundreds and i won't say thousands but there could be hundreds of steps in in large business situations so one thing that they have added is i'm not sure if i can find it here but if you right click on a step and go to properties you can add a description so let's try to add a description here and then see what happens so the description for this would be moved tenure next to date join so i put some description there and when you click ok it will be added and it will show up as a little i next to the step so it's kind of like adding comments in in your vba or coding languages so when you add a comment you will remind yourself what you are doing there but anybody else maintaining the file will also know so this is a good practice but it is not not directly accessible you will have to go out of your way to do this so i wouldn't really bother doing so much of this unless i'm doing something super com something super complicated or my query gets really out of hand for small queries where the steps are all visible on the screen it might be easier to just leave them as it is and and not bother but yeah if you want you can do it it's a very good practice johnnette connelly sends a super sticker uh big thanks jonette and i think this question appeared a couple of times manjuna sachimothi says how many languages do you speak kannada hindi uh i can speak hindi i speak telugu i speak english and that is pretty much it i like to think i can speak tamil but my wife will say that i i really suck at that so i i can't but i do watch pretty much all south indian language movies and i enjoy them so i can't speak them though anyhow we'll move on get into more power query stuff so this is a question from dr srinivasan um how to add conditional tenure for terminated records in the same tenure column so this is a very good question and what i suggest you to do is take it up as a challenge and try to do it not just you dr sean wasn't but everybody else so that you you want to fix the tenure so that it's not going all the way to today if the person has left back in december 2020 like this situation so how would you do that take it up as a challenge and uh if you have struggle leave a comment in the video below when when the video finally goes live in uh you know i could probably provide some more pointers but it's a good good challenge to think about let's see what is this how well does power query handle lowercase fields i deleted columns from a report and then field with upper lowercase had a trial and cash switching to the opposite case no delete solver problem okay now that we're talking about cases let's let's get real power query as i said is a strongly typed language it is also case sensitive so for it capital female is a different from small female capital f email and any other variations everything is a different whereas in excel for example you excel is not case sensitive except for few situations you may not realize it but let's say you are looking up for an employee name in a in a table using vlookup or xlookup you could type the employee name in any case and the vlookup or xlookup will pick it up and still match but not power query power query wants the exact thing in exact case if you miss the case it won't do that not just the values even your formulas and everything that you are writing not that we are writing anything here everything is auto generated for us but if you were to write anything they will all be case sensitive as they appear so this is something that is a very strict thing going from excel excel is more relaxed and chilled out it's like whatever man i'll do it so you can write lowercase vlookup works uppercase vlookup also works but not here if you say table dot select rows without capitalizing t and s and r it won't work it will simply give you an error i'll show you quickly here what happens reorder columns let's say you want to get creative and reorder these columns and you are trying to change something so you type small g here error where is the gender column i couldn't find it it was right there i just didn't spell it properly fix it it will work so this is something that you want to keep in mind when you are making changes if you are changing things and you are building stuff on top case is a big problem you will have to think ahead of how do i fix this once one simple thing that people often do especially within some situations is make everything lower case and then just deal with it and at the end of the process convert it into proper case as you want but sometimes you may lose information when you do lower case so you will have to be mindful about that okay so we'll go into that mapping file i'll just quickly open that this is also something that i've provided to you in the download files it's called in the zip file there is a file called bonus.txt this is how it looks uh i'm just gonna make this nice and big so this is how that file looks department bonus percentage you got procurement three percent website five percent sales four and a half hr four percent like that so this is the mapping table that i want to use to figure out how much bonus to give to each employee this will introduce you to two concepts number one how to merge two files number two how to add the column after matching so we'll do it like this we'll add a new query you don't have to go back to excel to add a new query you can do it once you're in the query editor you can just do it here this will save you a bit of time and unnecessary travel between the applications you can right click on this white space here new query file and then connect the text file i did say at the start of the stream i'll show you some steps in excel some steps in power bi uh but unfortunately we haven't been able to do that so maybe quickly i'll switch to power bi and then i'll show you how to combine files there just for fun because then you will see how closely these power query is replicated in power bi and power excel so that you will feel like where everything is in in that application as well so i'll open power bi feel free to post some questions or messages or whatever we are still doing the giveaway um in case you have just joined the stream or whatever there is a giveaway in the stream i'm giving away three power bi power query books well there are three different books they're the same book three copies so uh in this this giveaway is only open for the stream viewer so if you are watching the stream in your online you will have a chance to claim it so all you have to do is leave a comment or or post a message or yeah if you don't post a comment then i wouldn't really know if you're there so you may want to leave a comment okay so we will say within power b i get data i'm gonna get both files my staff data file and my bonus file i'm not gonna do all the cleanup on the staff data we'll just leave it everything as it is and just get the bonus so the first one is excel and uh staff data again the process is similar it will open the navigator screen from here we will select the sheet1 transform data and it comes into power bi here the screen looks largely similar there is a bit of yellow coloring instead of the green coloring on excel because power bi is yellow colored and these extra things here is a column quality that is something that i've added within power bi you can also do the same in excel i'll disable it and now the screen looks exactly similar everything is same so we'll name this as staff data and some of these additional transformations and there's an r script and python script running options available within power bi but not in excel at the moment i think but everything is largely similar there is some additional air artificial intelligence text analytics that you could do assuming you have paid membership into power bi program but largely it is similar so we got our department thing and we want to add that add the department as a mapping table so we'll click on we will right click here new query from a text file and bring that bonus text file as well and this will give you a text file navigator it will show you a preview of how the data looks like department and bonus columns it looks perfect for me i'll click ok my bonus comes through here so i got my department bonus i got my department column here all i have to do is take these two departments and marry them merge them so that's what merge queries button here does you can see the merge queries button it is you click on that and then it will open this thing you'll select the column on which you want to merge in the first query select the second query and select the column that you want to merge on so you want to match department here with department there it will give you a preview of i could match all the rows if it cannot it will also tell you that you can also select what type of merge you want you want the left outer or um inner join left hand tie and right antenna etc if you are not really sure what this means then you should just leave it as it is but if you are familiar with those database words then you can go and change it depending on what you are trying to do in this case we just want left join which means the table on the left which is the top table will keep all its rows and wherever there is a mapping value in the second table it will bring that over so we'll click ok it will add the bonus column here as a new column i'm just going to move this right next to department so you can see what's happening here on the screen and let's just see so it'll add a bonus column notice that this column will not have individual data it will have a table okay this is actually introducing you to another powerful idea which is a cell within power query column row is not necessarily just a single value it could be a table it could be a list it could be a whole heap of different things again a big big can of worms that we will not open right now but that is there i'll turn off my face so even though it says stable it would be a single row table so if you select that you will see that this department here maps to this this particular row here which is sales and 4.5 percent bonus so that this is the corresponding row of the bonus table that is mapped out here so we will expand that column we'll click on this double sided arrow thingy and we'll just select i want only the bonus percent and we don't need that we won't you can use it but that's not really needed and it will give you for each department what is the bonus person so you can see that automatically it fetched the corresponding bonus you could kind of think this as a very long-winded vlookup and you wouldn't really be wrong wrong uh and we got that so now all i have to do is take this bonus percentage multiply with that okay so we will select the first column hold down control select the second column both columns are selected add a column of standard arithmetic operation of multiplication you selected both columns so i simply want to 4.5 percent of that and we'll get a multiplication here for some reason power query wants to turn that into percentage so it was percentage formatted but i'll change this type to a decimal number and then double click call this as bonus 2. so there you go how you can combine two tables to get the bonus i hope that is good we'll just switch back here to see what is going on murray sense sensing a super sticker thank you mary and um some interesting messages here tarang says i'm looking for one very good and best friend for lifetime and my mom is keep saying that books are our best friends in lifelong please give me my friend to me oh well uh we'll see there's a lot of you asking for books i only have three books to give away so this is what uh gunjan my winning speech thank you chandu for awarding me this super book i would like to thank gil ravi for writing such a great book last but not least youtube for this live video you haven't won it yet david says looking forward to the giveaway i can't wait to receive thanks david thanks for sticking around and so that is how you can combine two tables now i do have one more thing that i want to show you but i'm very mindful that it is already one hour 52 minutes into the stream and i really like to wrap things up before two hours so i'm unfortunately gonna stop here we will pick it up maybe in the next live stream uh the reason why i'm mentioning it is when i gave the download files i have actually another file called hyderabad data i'll briefly explain what i wanted to do i won't actually do this now because it's it's gonna take another good 15 minutes and i think it's really unfair to stretch it that far um but this is about the the data is for this made up company called awesome chocolates which is the same company that i use in all my courses and the data set is for our head office in let's just say sydney and they recently opened another branch in hyderabad so the hyderabad staff data came to us in this text file as you could see their data is maintained differently they got id so it's not employee id it's id name and gender in one column they don't have age they have date of birth then the department date of join they nobody left so they don't have termination date but they have salary so we would like to merge this and that together into one big table that sort of an operation is called append and get the final data but as you could clearly see this data is different from that so what do we need to do to make this data look like that data and then combine them that's the challenge that i really wanted to show uh but unfortunately we we are really way out of the time limit now i i wanted to really i'll do all of this in 90 minutes but hey we had fun i hope you enjoyed it and you learned something new but that file is given to you the hyderabad data text file so feel free to take it up as another challenge and if you have some struggle you know you can post a comment or and maybe others can help you out or whatever but i will talk more about how to learn power query because i think that is more important than just uh you know learning few more things we did cover quite a bit of ground so there is a lot of value in there and i don't want you to feel overwhelmed when we finish all of this so we'll close this guy here yes and i'll save this power bi file as well parallelly later but for now i'm gonna switch to my slides let me just see what is having so naina raj says good to see comparison of excel and power bi uh and thank you uh thank you for saying that that's really my intention i want to show you that it is the same tool that is available both in excel and power bi and this is the big reason why i feel like if there is one thing that you want to learn this year make it power query especially if you are watching my channel you are here chances are you are already doing data work so power query learning will will take you more ahead than anything else that you would learn this year so definitely prioritize that excel work says if i don't get the books i will settle for a box of chocolates from awesome chocolates thank you excel works uh you will just have to walk in and buy a box of chocolates from any other store and just think these are awesome i think chocolates are awesome respect to where they come from and cassandra says thank you very much chandu it is great always uh thank you cassandra parvet says enjoyed this session chandu thank you povege and this is a super sticker from gilbert thank you gilbert good to have you on the stream and uh awesome to help you out as well were you in hr when you started your career i not per se in hr i think everybody kind of meets hr when they start their career but i'm not in a hr hr situations early on in my career but i recently for the last three four years most of the consulting and client work that i did is in the hr space so that's how i picked up lot of human resources analytics and situational stuff and uh that's also the reason why i try to bring those examples as as and when possible okay um we will [Music] go and let me quickly talk about more ways to learn power power query because this is super important that you give it a bit time and and set aside uh some activities to practice and all of that so i'll talk about some of my favorite ways [Music] so three things books channels and courses the books that i recommend are right now i'm recommending these two books there is a caveat though this book is already available collect combine and transform data using power query in excel and power bi this is by gil ravi again i don't receive any commission for recommending these books these are the books genuinely i think are extremely good i have read them i use them and i i stand by them so get them okay the second book is master your data with excel and power bi by ken puls and miguel escobar this second book is actually it's not yet out if you go to amazon you will see that this book will be released in in whenever i think it's supposed to come out now but i think it's going to come out in october so i'll tell you a small story now i emailed both gil and ken when i announced this stream because i kind of uh i'm not trying to name drop or anything i know them personally so i emailed both of them saying that hey i'm doing this live stream at the end of the month it would be awesome to give away some of your books you know if you have got any copies let me know otherwise i'll buy them on amazon and i'll still do the giveaway gil immediately sent that but ken came back and said hey the book has been not yet officially released we have only released the digital copy and we are not really figured out how to distribute it for complementary basis and all of that so then i told can okay wait we will wait for your book to come out because i would love to buy the physical copy as well so i'm gonna do a giveaway on ken's book for maybe an upcoming stream i don't really know when that's gonna happen but if and when it happens maybe it's for christmas or or the thanksgiving time period i'll do the giveaway for that but for now i'm giving away three copies of ken's gills book but these two books are good in fact i showed you uh in my previous streams i showed this book so this is the version one of that monkey book you can see that little monkey they've changed that now but this is called m is for data monkey because m is the programming language that is used in power query so this was their version one of the book this is how i learned my m language and when i moved from india to new zealand this is one of the books that i carried all the way along in because this is such a valuable book to understand and use and apply the m language techniques so if you are really looking for a book you can still get this older version but the big problem with power query and power bi is every month microsoft is changing them adding new versions they renamed the ribbon as get and transform data added buttons removed there was no pdf option five years ago but now there is so things change so if you're reading an older book you will not know uh everything and you will also use a longer and more complicated way of doing certain things than what is available today so this is the reason why i think getting a latest book is useful but book is not the only way to learn there are other ways as well so my recommendation is if you have enjoyed this stream but if you are feeling more thirsty for power query and if you are feeling a bit annoyed with all the dad jokes and you just want to get to the point not be interrupted by all these chat messages or whatever else just want to learn then watch this video this is something that i created one and a half years or two years ago now one and a half years ago power query it's like a 90 minute tutorial where i show it to you how power query works with four full-length examples so many of the concepts that i covered here are part of the first example and then there's three more examples where it goes quite in-depth and it is on youtube so just watch it i put a link to that in the video description if you feel like uh two hours with chandra is not enough i need to get like another two hours of him that is the video where you can go and watch be warned though you will see me in full beard when you watch this video this is because i created this video during the early covet lockdown times last year and uh and at that time i also decided that i'm gonna just go grow beard so of course you won't see me throughout the video at the intro section i'll show up and then that's pretty much it after that it's all excel so that that is one place but if you like youtube and you want to learn power query these are some of the channels that i frequently go guy in a cube karbal la lagarani excel is fun they all they're not exclusive power query channels but they talk about power query as part of many other things so feel free to check them out and enjoy their content some of them have dedicated power query playlists others do have one or two videos here and there you know just check them out they got some amazing content as always and last but not least if you want a more handheld course kind of thing where you will see how power query is used in the overall process flow then please consider my online class power bi play date it's a online power bi course where i teach power bi but as i said earlier in some of my other videos and streams power bi is not one thing it is part of power query power pivot and the visual layer so this play date basically goes into all the three layers and explains to you how to use them together with many real life examples if you are interested there is a link for that in the video description check it out and if you want to join email me the course is actually currently closed but we are just letting people in the back door so if you want to come and join us uh just email me and i'll tell you how to enroll into the program all right that brings us to the giveaway now i haven't really figured out how to pick the random winner i mean i could kind of scroll through this and stop wherever my mouse stops and do it but i feel like this is unfair especially because there's lots of comments so i'll have to think more scientifically but for now i'll just switch to the comments here just to read up what is happening with everybody else and then i'll announce the winners valentin says power query is new to me it did seem like a lot i saw online that power bi is essentially power query and powerful word combined yeah you are in rom power query is uh one of the core critical components of power bi ecosystem and the point of this stream is to not overwhelm you or anybody else hopefully what i showed gave you enough courage to click on the buttons play with things and see how it pans out a good idea would be to always keep backups and you know keep trying the initial like i didn't learn power query in one stream i it took me good three four months before i actually understood what it is and how it can be applied and then i started using but it is also because i have got a good database and sql background so for me it was easier to correct the dots but if you you're coming brand new then it may be hard for you to really see how and where this would fit in so don't beat yourself up enjoy the journey take it slow and you will really appreciate it uh all the effort that goes into it when you when you do it pratish says after first time here after getting a lot of emails since a while enjoyed and learning few stuffs thank you chandu thank you pratish welcome and hopefully we'll see you again next next time jakoda says huge thanks hindu well spent two hours for me thank you so much it was really fun talking to you and rest of you others on the stream for two hours priyanshu says can you tell us your linkedin name i want to connect with you um you can just find me on linkedin just search for chandu uh um i don't really know what sort of benefit you would have by connecting with me but feel free to follow me there uh but yeah i i connect more with my youtube audience here because it's stream whereas on linkedin i randomly post one once in a while a good place if you are into social media is to connect with me on twitter uh again i'm not really trying to gain any twitter followers or anything through this live stream but it's just so happens that i post more often on on twitter so my user id is r1c1 it's basically row one column one that's the old notation of referring to things in excel and you can follow me there i post quite regularly there including just before this stream started so feel free to check check me out there if you really want to connect georgia says great session thank you chandu you're welcome georgie um you explained it well thank you pradeep thank you i hope you enjoyed it as well um henry from dominican republic great tutorial chandu thank you all right uh there's quite a few thank you messages and i'm really humbled and and thankful that most of you have chosen to spend some of your friday evening with me to learn power query uh share some of your witty comments and jokes and stick around and watch me explain things so that you can also learn and apply this now time for us to figure out the winners i don't want it to be like a random mouse click so what i think will be fun is use power query to pick the winner i know this is gonna drag for another five ten minutes feel free to hang around if you if you got things to do you're more than welcome to leave what i will do is i will pick winners and then i'll just say the names out on the chat window in the stream if you are there you message me back if you're not there we'll go and keep picking things so how i'm going to do it is i'll i'll show it to you [Music] just give me a second here where i figure out what i'm trying to get shareable link so this is what i normally do for every live stream as well when the stream finishes because there's lots of comments and i get to read some but not all of them there's usually more than 500 600 comments when i run the stream when the stream finishes i run a small one-line python script i talked about this in another video as well what this script does is it will go and fetch all the live comments as a text file so i'm going to run this stream right now so any comments you post after this point of time will not be eligible for the book giveaway but um hey you are more than welcome to still stick around say say nice things or say hello or whatever but i'll fetch all the comments up until this point and then we will get the text file we will load the text file into power query we will then ask power query to randomly sort them but first we will remove any duplicate comments from the same person so if you have posted multiple times your name will only appear once so everybody has equal chance and then i'll pick the randomly sort and then i'll just read out the first three names sound good all right let's just do this so i'm gonna open my command prompt here i'll just share my screen as it happens and okay go to this folder if the screen is not readable that don't worry you know you're not meant to read things that are there and okay so i'm running the script it is doing its thing it is extracting oh what is this this is the first time i got an error so i'm not really sure what it means it didn't really work okay i've never tested it while the stream is running live so it could be because of that normally it works well when the stream is finished all right this of no good i don't want to waste your time now okay i'll just read out some random names and and what i'll do is i'll give like two now and then the third one i'll do it when the live stream extract can be done so the only problem with this approach is i can't even see all the comments it kind of stops at the last hundred now so it is really unfair but i'm scrolling scrolling scrolling okay that's the first comment pradeep kumar if you are there uh leave a comment now and i'll i'll just tell you how to get in touch anyway but um yeah so that's the first one we'll see and this is the second one murray and that's the third one with yashri so if your name appeared feel free to reach out to me and then i'll see how i can get you the book so pradeep kumar with jashri and mariji and someone asked this question which is can you share the python code well it is not a python code that i wrote it is actually called chat downloader so if you look up online you will see that uh there dj we say sounds like a great demo of power query to use it for the gear well it would have been great it's just uh i couldn't run the script because it came up with an error uh i think it was because the stream is still going on so it couldn't fetch the comments it will only work when the stream is done which is weird but hey so so that says why don't you do it afterwards why do you have to do it now i thought it would be fun but i'm sorry i didn't test it yeah charlie says you said third will be after the chat ends i know i did right uh maybe i'll give the fourth one after the video ends so matt ferguson says great session thank you matt uh i'm glad you enjoyed it and uh i had good time as well um there were very few technical hiccups and excel has been super quite all along she was not making any fuss after that initial few minutes of getting confused with what's going on now so pradeep kumar comes back and says i'm there thanks pradeep all you have to do is email me my email address is on my youtube about section just find it and send it send an email to me and then i'll forward the download code to you same for the other two if you have if you are here just email me and i will announce one more winner in the chat comment thingy later on um what happens is usually when when you have like a thing like this giveaway and you announce the winner sometimes the person is no longer online so they never see it and they never come back so if i don't hear from either of those two i will also give away those books in the chat window so just check back after a day or two and then you will see your name satish says can you suggest any books on power query i thought that's what i just did well you're not watching it mate all right here is my book recommendations again coming up on the screen these are the two books i've put this links for these two books on the video description already all you have to do is just look at the video description of this stream it was already there but when you check it again you will find that these two books the other names are also there you can go to amazon link from there but if you want you can find it from other booksellers as well excel works says another super session chandu picked up a few things i did not know and congratulations to the winners well done everybody big thanks to excel works as well you are throughout the session hanging there and posting messages and helping others as well so thank you so much for that and uh lots of love to you this is from judy and jeff i think when you have a new file like with the next month's data how do you bring it in and have the new changes applied do you have to pull it in from the same place so the way we have done it in this example is we we worked under the assumption that when the new when the new file comes it will be either replaced by this file or somebody will change this file and add the extra data at the bottom so that is the assumption but in many real-life situations what could also happen is every month you will only get that month's employee data as one file so july one file august one file september one file and in such situations you would have to use another option that is available in excel um go here close it keep so if you go to data get data so we were using this option from file but if you use this one from folder this is basically kind of like that it is a folder automation option wherein you say that all my files are in this folder can you go and get everything they all have the same format just combine everything and give me one big file and it will do it i have not shown that method to you but the other power query window video that i recommended uh it's basically an hour and half long i think i showed some of those examples there so check it out this is a message lots of love from india thank you i i hope you enjoyed the stream uh i have this weird idea in my hand my mind that maybe i should do like a telugu only stream uh just to connect with some of the telugu audience and but i don't think i'll ever do it maybe i'll do it sometime i don't know we'll see where that goes this is a different question that is not related to power query ram neva says where to get power bi publish to web feature for free um so i'm not really sure exactly the steps that you need but it is possible microsoft keeps changing the way this is done from version to not version to version but over time so you will want to just search online for that exact thing to find out what is the latest instruction but i believe it all begins with creating a free on microsoft account or something like that which will then you can use to set up your own power bi online workspace and from there you can publish so this is how i do it now savio says thanks from argentina big thanks to you sergio and all right i think that is pretty much it i hope you all had fun i had loads of fun as well my coffee is nearly done and yeah that's pretty much it so i'll see you all again at the end of next month with another topic if you've got any topic suggestions for the end of this month uh end of the month live streams let me know in the community page where i usually ask a question every now and then you can also leave a comment on this video if you want to see something else explained but uh yeah i've got things like i always think about like what am i doing next month and i i try to jot down some ideas but i would love to hear from you if you want to see me explain or do something specific and as always i try to keep everything under two hours but every every time i do the stream it is quite a lot of fun and you know things just keep going on and on and even after two and a half hours almost we still have 170 people watching the stream so i'm really humbled and i'm amazed by all the love and support that you show on on this channel and my work thank you so much i hope you enjoyed it and i'll catch you all again next month bye bye and i will of course upload the files uh in the stream uh this video description maybe a couple of hours later once i go downstairs take a nap and finish my breakfast and come back upstairs all right there's already suggestions like charisses m language sure uh matt says great session really helpful for beginners for like me thank you matt and while anton says keep well everyone yes of course keep well uh um i'll just share quick personal updates as well august has been an amazing month for me i did a mid month live stream some of you might have joined where i unboxed the 100 000 100 000 subscribers play button i think you can see it now there that's its uh home now right next to my office plant and so i did that at the 15th or 13th of august or something like that and we had quite a few people show up with that so that was good and uh recently for the last couple of i think a week or maybe eight nine days now new zealand has been under the lockdown again due to a small covet outbreak so we are all home not doing much of course i'm home all the time i'm working from home for last so many years so for me it's not different but i think that is the reason why excel is a bit confused because everybody is home all the time so she's a bit stressed that she's not getting her own alone time to just sit and enjoy the sun and you know do nothing all right uh yes of course you all have a good weekend as well um i think said geo battery's keyboard is stuck because he's keep saying book book book and mike brown says is there a best resource out there for m code the the best resource that i could think of is this book but this is actually a few years old now so some of the language syntax and the methods that you that are used are a bit old for what is happening nowadays i think what what would be better is don't think of learning m code instead think of using the power power query screen interface because a lot is possible just with clicking buttons and let's say in amp code you could write one step and in in screens you can do it in three steps i feel like those three steps are a lot better than doing that one line m code simply because um the the m code is is not one of your usual languages and it is a very specific language that you cannot reuse anywhere else whereas if you take something like excel formula language it is more widely available versatile and all of that if you take general programming like vba the concepts that you are learning can be applied in multiple places but the m code the m language is is really just designed for power query so whatever you're learning there you can't really apply elsewhere it doesn't really work like a traditional programming language so for that reason i would say hold off on learning m until you really have to do it but if you have to learn then there's plenty of online resources i think chris webb has a good blog where he talks about m language things i'll probably add a link to that in the video description later on so yeah good luck with that all right that's pretty much it guys i think uh i'll wrap it up now good to have you all in the stream see you again next month and stay safe enjoy your week and and good luck with park ready bye

Channel: Chandoo

Views: 21,324

Keywords: Power Query, Power BI, Excel Automation, data cleanup process, data cleanup in excel, data cleanup in power bi, data extraction cleanup and transformation tools, power query advanced, power query in power bi, power query transform data, power query editor, power query m code, power query connection, power query m language, power query change data source, power query magic, chandoo, live stream, excel power query

Id: t43kb7BRsFY

Length: 143min 16sec (8596 seconds)

Published: Fri Aug 27 2021