 Hello and welcome to another D Brown consulting webinar for the Excel and Power BI webinar series. This is the September 2018 edition, and we're going to talk about 10 things you didn't know Power Query can do. Right, so what are we going to talk about this month? It's all about Power Query, so we're going to do an introduction to Power Query for you that don't know Power Query, and then we'll look through 10 things you didn't know Power Query can do, or maybe you did, but interesting things about Power Query. First thing we talk about is call up function and help, then auto matching, headings, and consolidating sheets, data exploration, summarizing data, consolidating from a folder, and then looking up when you're trying to look up two tables when they don't have a unique reference. It's like a VLOOKUP without a unique reference, but VLOOKUP in Power Query. Creating columns from example, something called Flash Fill in Excel. You could actually do Flash Fill in Power Query now, very cool. Using the choose column button, so now I mean you could filter out rows, now you could filter out columns, very interesting. View relationships between queries, you want to see almost like a data model for the Power Query, something like that, we'll see that, and then consolidating worksheets in Power BI. It's a bit different than consolidating worksheets in Excel, see the slight difference between the two. That's what we're going to do this month. If you're following, just sit tight and enjoy. Let's move on. How many of us here have used Power Query? I'm jumping to the next step now. Power Query, introduction to Power Query. This is Power BI Desktop. This Power BI Desktop and Power Query, if you go to home, you see Power Query under your get data. Everything external data, enter data, so this get data is Power Query. If I open Excel, Power Query was born in Excel, really Power Query is an ETL, Extract and Transform and Load tool. It's an excellent, excellent tool. In fact, most people always have problems with their reporting because their data is not structured right. For the really very advanced users of Excel, they use VBA and use VBA to clean the data and stuff, but it's not as versatile and cool as Power Query. Power Query is excellent. Even in Excel, so it was born in Excel. If I show you Excel, this is Excel and Power Query is right there under data, get and transform. All of this is Power Query. It's exactly the same thing as I just showed you. Everything here is the same as the two Power BI Desktop. What we're going to do Power Query, let's see, what's the first thing we're going to do? We're doing 10 things you didn't know Power Query can do. And number one in our list is what? Let's see, hold the control and click. Well, guys, now clicking, let me just manually go to number one. All right, so number one is call up function, help and using M IntelliSense. Call up function, help. So let's assume you don't know the function you want to use. Let me just go to a blank query. How do I get a list of functions? I don't know the function I want to use. So if you go to a blank query, you simply type equals to, you type your hash key and you type shared. Yeah, equals to hash key shared. Now I don't know why they called it shared. They should have just called it equals hash key Power Query functions or something shared. When I enter, you will see a list of everything Power Query. I mean, all the functions are there. So the nice list of functions, all sorts of stuff. So really, if you want to see this as a table, let's convert it into a table. Yeah, so I convert it into a table. And if I was in Excel, I could just load this into Excel and you see all the list of functions. But really why I did that is if you click on any one of this, you would see the explanation of that function, really. So let's pick something. Let's do, let's come here and do a list. We're going to look for a function called list dates. It's going to list dates. So I'm just typing list there dot maybe dates. So here, this is the function I want to see this one. So if okay. So this list days, I'm just going to click on the function button here. So this is the function. So I just click on the function button explains what the function does. You can read everything about it here. So let's create a list of dates, right? I want to create a list of dates. Typically, you click on this button to say what is your start? When are you starting? What are you starting? So let's assume I want to start on the first. Now look at this, the input value one September did not pass using local English United Kingdom and short date format DMM MYYY. Hmm. Now, all this is telling you this is quite annoying. This is saying that the way I'm writing my date, the computer basically knows it as United Kingdom dates. But this system is looking for this button here is looking for an American date. So I just, I just have to manually type let's say one slash nine slash 2018. Now, for most of most of you to work fine, but for me it's not working fine because I'm using the UK dating system. My UK date, if I go to my control panel, you'll see that my date is UK, but I won't go into that. It's looking for a U.S. dating system. By the way, one nine 20 18 is my computer's dating system count. Let's say we want to start. Let's say I just want 30 days, right? 30 days. And then my step is one day each. So 30 days starting from first of January. Give me 30 days, one day per interval. Okay. So see what it does. It gives me first of September to the end. How did I know that? Because I went to look for a list of functions. I wanted a list of dates and we see now I have a list of dates. I can now convert this to a table by coming to the top left and saying to table. So now this can just ignore this and say, okay, so this is now a table. I can double click this heading and say this is my date. Enter. And then I can convert this to a data type of date instead of any I call party to date. And I'm in business. So this is my date and I can close. Let me close and load close and apply or just apply what I've just done. Let's call this a date list or well, I've converted to a table. So it's not really a list, but when we date table, right? So date table. So you get the idea. But really, we got this by using function we identified, we had a list of functions or something, list functions. Yeah. Now it's our first trick. So I enter. So list functions that we have it. Now if I go into the view section here and I go to the advanced editor, you will see all the M code in here. See all the M code. Now this is the new look M code for the September update. You have all these colors and everything because it's now has IntelliSense, right? So it has IntelliSense. Anything you do, I mean, it's kind of will help you really helps you move on. So look at this. I'm typing name. You can see all the different help here, date dot month dot name, anything to do with name in Parkway will come out. So this each, for example, I mean, this is, I could delete that just to show you. And if I type each, I'm starting to type is giving me some help. Well, not very smart help, but well, you still help. So each. So once you type your queries now, it gives you like Excel, it helps. So that's IntelliSense. Really cool. What you say, now you sure you want to cancel? Yes, I want to cancel. That's close. All right. So that's my first query, guys. Let's go back to Power BI and see what's the next thing on the agenda. So that's number one. Number two, not apply any changes. Let's say number two. The second thing is we want to auto match headings and consolidate sheets in Power BI. What do I mean for me to show you that I need to open something in Excel? So there's usually a problem in Excel when you're consolidating data, when you want to auto, auto match certain things. So let's say this is my spreadsheet. And right now I have my name, my department, my title, and then I have total gross, basic housing and leave, right? So G has leave, for example, if I go to August, August G for August is not leave. So let me zoom in a bit. So you see that. So if you check my August, the column G is housing. In my May, column G, what's column G? Let's go my May column. Column G is housing, okay, well, okay, it's housing for G, right? In June, column G is not housing, column G is transport. So column G is transport, see? Right. So here we have, if I go to March, let's even go to March. If I go to March, let's see what does March have to offer. So in this March sheet, March is even a little bit more confusing because if you look at March, it says the first column A is title, name, and then department name. If I go to April, column A, you know how very annoyed you are in your office when someone gives you a really hopelessly scattered table. Look at this. It's name, department, then title. But in March, it's title, department, and name. And then everything else is scattered. We know that the tables contain everything. It's just that it's all kind of messed up somehow. So power query will help solve this for us. So let me close this. By the way, if you want to do a query for an Excel workbook, or so you have to make sure it's closed, has to be closed first. So we're not doing the query from Excel. We're going to do it from here. So auto matching headings, that's what we're doing, consolidating sheets in Power BI. So two things in one, we're auto matching as well as consolidating sheets. So what do we do? Well, we need to connect to Excel because the data is in Excel. So get data from Excel. So where we're getting data from, I have all my data here. And this is its auto headings recon. So I click on this, and I can just double click on it. So it's going to open it. So I connect to the auto heading sheet, right? So all of this, we want to kind of consolidate it. So you click on the auto heading. Now, if you click on the auto heading, if you look here, I can't edit. Now, what's happening? I can't edit in Excel. I'll be able to edit. Really, there's a trick. All you need to do is right click on the folder, this folder, and that's the edit. So as you say, edit. All right. So it's up. There we go. So we've opened it. And now we need to kind of consolidate it a bit. Right. There's something about this trick. When you want to consolidate and keep the headings and let it, you look at the headings and everything, that's only works when you're using table. Now, for those that know Excel a lot, in Excel, when you have data, when you have data in Excel, if you convert it to a table by doing, by just clicking anywhere within the data and going to insert table, there's a special thing that table does. So this data is all the same, right? But this is a sheet. This is an Excel sheet. And then this is a table in Excel. So we need to filter out the sheet because it doesn't work with sheets. It walks with tables. So you need to convert every single data you have, every single sheet, make it a table, just control T, control T, control T. Just take note of that. So we are going to leave, we need our data. And I think we need our item. We need to know what month it is. We just want to have that. So I'm highlighting this holding my control key and clicking the next thing and then removing other columns, right? So remove other columns. And then we can now expand this. So we expand. And usually I like to remove this original name prefix. We don't need that. So we have all this data here. We're expanding and we'll say, okay. So I've expanded. And if you look at it, we have our name, department, title. Believe it or not, everything would have consolidated nicely. And then we have the item there telling us which month it is, right? So this thing is, you'll be wondering how possible is this, right? So if you remember, name, department, title, right? So this department police, but let me filter by March. You remember that March was really kind of hopeless. Let me load more. Let me filter by March. And March and June. Let's just see March and June, right? So let's go left. So it's in name, department, title is properly done. It's changed it and made it all look and feel correct, right? So let me remove my filter. I'm going to delete this step. So this is the query. This is the query for consolidating. Pretty simple and pretty cool. So that's auto headings recon. So that's our second query. Let's just apply. Let me close here, apply. So we'll move down to, let's get back to our Power BI to do the next one. And then we'll do the next one. So let's see the next one. So data exploration, understanding data table profile. So sometimes you just want to see the profile of whatever it is. What's this query? Let's have a look at what it is all about. Just a quick profile look. How will that work? So let's check. So let's use a query to determine, let's, the last query we did, let's see if we can check the profile of the query. Okay. So let me go into get data. Let's go to the query. How do I quickly get back to my query, edit queries. Let's view edit queries, data source, query, query, query. Where's view my queries? Have a view button. See, I'm used to the Excel bit where I can just view it. So anyway, I can view my queries. I have to just right click and edit, but there's a view query button. So here we are. I want to know the profile of this query. So let's duplicate this query. We're going to duplicate this query. Okay. So I've duplicated my query, right? And so this is my query. Well, I've, well, let me just find what profile is this query, this second query. Let's test it. So let me come to a new query. Let's go to, I'm going to add a new source, right? New source, I'm saying blank query. So I'm just going to write a blank query. And this query, when you want to test the profile for something, you just say equals to in the blank query equals to table, unspelled table, table dot profile. And then I open my bracket. And normally I open my bracket and then I just type the name of the query, right? So I test in auto headings recon XLSX. So if I do that, auto heading recon XLSX. Now close my bracket. Unfortunately, I don't think this will work. So let's look. See? Yep, it didn't work. And that's because our name has spaces. If our name didn't have any spaces, it will work fine. But because our name has spaces, we need to do some funny additions to this code. We have to put a hash here. Then we now need to put a double code. And then we go to the end of the code here and now put a double code. And by the way, you shouldn't put a dot in between this recon XLSX. So if I enter should work this time. So now it's given me analysis of this data saying that minimum April set, of course, we won't have that basics and stuff. It's not really useful, but this just identifies or gives you detailed analysis of that query data. So here, if you check, I mean, sales department, like education, the minimum is null, null, null, null. All of this is null. If you look up at the data, the one that is actually working here, look at it, the count 1.2, 1.2. This anytime you see ABC123, ABC123, it's very annoying. ABC123 is a very, very wicked data type. Let's go back, come here and check. Here we have ABC123, ABC, these are all you should always do when you do your queries, highlight and change the data type. Never leave any, any is terrible. Make this text. Now these are ABC123, ABC123, you can do maths with ABC123. ABC123 means any. Make sure that your data types are the end of your query. Once you finish your query, highlight everything and make sure the data types are correct. So a decimal, maybe this is a decimal number, right? All these are decimal numbers. This guy can be text, which is already text. So that's key. So I'm going to apply this, apply. And because this is my new query that I did depends on this. If I come back to this query, and then let me apply. So now, I mean, look at all this. No, no, no, no, no, no. That doesn't make sense. It doesn't make sense. And here we're talking about, if you look at this, this is auto-reheadings recon, reconXLSX. And I think where I went to do all those corrections was in also heading reconXLS2. So I didn't correct this one. So this one still has all those ugly ABCD123. So we do that same thing here. Or I could actually change the query. So this query should be looking at the one that ends in two, right? So if I come here and type, type two, remember, we clean the data, I mean, the data types for two to enter. Yeah, but you see that's working now, right? So too much duplicates kind of gets people confused, right? But anyway, so this works fine. Let's call this one profile. Let's call this profile. All right, so moving on, let's do another one. So moving on, let's get back to Power BI. What's the next thing on the list? So we're going to summarize data. How do we summarize data? Let me go back to our query. Let's see if I can borrow a previous query we've used. How do we summarize? So, yes, if I look at this auto recon, we know that this auto recon is from January to December, right? If you remember this January, if I load more, lots of things, this is all this month. So what I want to do is just consolidate everything. I don't want to do it in Excel. I want to do it in Power Query. So if I want to consolidate, basically that means I don't want January, but I just want everything together. So, frankly, before I do that, I will need to delete this column. I don't need it by months. I'm going to remove this. It doesn't add any value. And if I go to the beginning, I'm going to consolidate everything. So the first thing you should do is all these numbers, all these values you see should be in a single column. How you do that is you highlight your text. This is your unique ID for the transaction. Highlight all three of them. You right click and then you on pivot other columns, whereas on pivot other columns. So you've unpivoted this. These are now your line items, your payroll line items, pay lines. And then these are your values. Now, can you see that ABC123? It's so annoying. If I leave the ABC123, it's going to mess us up. ABC123 means anything, any value. Never ever use data type of any. You will go very far in your Power Query analysis. And then you're like, what's going on? It's because you can't consolidate with any. You need to consolidate with decimal number. It's fine. Decimal number. So change that. Very important. This guy can see ABC123. In fact, all of these texts, all these are texts. So really, all this should be text. Right? So data types, just note one thing. Data types, data types, data types are extremely important. So how are we going to do this? We need to do something called group buy. Right? So here we are. Now, what you want to do is tell it to consolidate values. Right? So if I come to group buy, really, I'm going to group buy. So group buy is like consolidation. So group buy. Now, if you notice here, group buy, pay line, you choose which pay line. Oh, am I grouping by? The thing with group buy is you need a unique identifier. After uniquely identify each line so that when we're grouping, we know that look at this guy. All this Cabra Michael, Cabra Michael, Cabra Michael, we need to sum up all of this Cabra Michael. But it's not just Cabra Michael. Well, Cabra Michael, police, maybe there are two people called Cabra Michael. But hopefully there will be one Cabra Michael as the police department and is a captain. And we want his transport allowance. And guess what? All his transport allowance for all the various months we run that not just this is just one month. All the transport allowance for every single month. So we need to kind of join this and join this and join this and join this. Then we now group. Now, I think it's always better to use advance to go to advance. Then you're saying I want to group by, let me say name, they add, I want to group by department add. Now, what I see is this is kind of a waste of time. Before you group by just highlight all the columns, you know, you want to group by. So name to a line, you know, all of this should be grouped together. And then you're now consolidating the value, right? So once you do that, click on group by and it goes to advance immediately and types everything for you. So that saves you time. So right here, we're here. And what are we doing? We want the total or the summary. Really, this is the summary. Yeah. And here I go to my operation. I want to sum. What do I want to sum by? I want to sum by the values. So we're grouping all this, and then we're giving us a summary, which sums all the values, right? Say okay. What happens? It's basically going to give us, this is the consolidated gross pay. This is the consolidated basic, the consolidated transport. Everything is consolidated, not monthly anymore, fully consolidated. So that's how you consolidate, right? And I can call this, oh, I call this. We're going to call this consolidated or summary. Let's just call it summarized, summarized data. Summarized data. I also advise you don't use spaces in your names. Our query doesn't like that. Anytime you use spaces, the code just changes. The code you use in another query will change. So anyway, summarized data. That's cool. So what's next? What's the next trick? So trick number four or trick number five, down four, trick number five. Just to recap for you guys, the first trick was call up function help and use the M intelligence, the new M intelligence for September 2018 Power BI update. Second trick we did was auto match headings and consolidate sheets in Power BI. The third trick we did was data exploration, understanding data table profiles. The fourth trick was summarizing data. And now the fifth trick is the next one we're doing is consolidate from a folder. So we'll do this trick next. Okay. So consolidating from a folder. So we need to go to query and see how we can consolidate from a folder. So I come to home. I go to edit queries, edit query. You know, we've already done four nice tricks. If you are following on, this is trick number five. So number five, we're consolidating from a folder. So I'm going to add a new source. Now, if I was doing this from Power BI, it wouldn't be adding new source. I'll just do a fresh one. But anyway, let's add, I can add a new source. In fact, let me show you that. Or in Power BI from here directly, I can get data. So it's the same thing really, it's still going to go to the query. So get data and we're consolidating from a folder. Now in Excel, it looks a little bit different, but Power Query is just the way it looks. So I need to go to more here in Power BI. Go to more. And then I can check for a folder. By the way, in 2018, now you can consolidate from PDF. This is just crazily amazing. I mean, I have one file that has about a thousand pages for the government. And that's the government's budget. I'm going to go look for that file and see how I can extract everything from that file into Power Query. So you can consolidate in a folder. You have some tables in the folder. You use Power Query to pull it out of that PDF and then pull it out of the PDF, consolidate from PDF. Anyway, that's cool. Probably next webinar as we'll see how that works. We right now want to consolidate from a folder. So click on folder, click on connect. I need to browse to go and find where that folder is. Browse, I know it's on my desktop. Power BI, I have consolidated folder. So that's this one. So I click OK and say OK. So it's going to get ready to edit my folder. It's going to open Power Query. Now I can do combine, but really I don't advise you do combine. Just go straight to edit and let's type the code ourselves. Okay. So we're going to edit. Look at all this. The data I'm consolidating, payroll data, edit. So edit opens Power Query. I have everything. Really the key thing you need is this binary. All this is just extra, extra information. You could leave the name if you like, but really I just want the binary. So I'm just going to right click and remove all the columns. And then I expand this a bit and see what happens. We're trying to expand. Now look at this. This is the data here, but really I need to extract information from this. Expanding really won't help me. I need to extract data from inside this binary. So I need another column. So I'm going to go to add. I'm going to go to custom column. And I'm going to add a column. The name very doesn't matter. It's really, I want to get out the workbook. So we say we're extracting, right? Let's just say we're extracting. Let's type we're extracting something, right? Now there's a code, a function to extract your workbook. It's called Excel dot workbook. Then you open your bracket and then the context, right? Now here's where there's another trick. Now, if you followed good practice when you're creating your data and all the data that you're extracting are inside tables. If they're inside tables, well, that's fine. Or if they all start from row, really they all start from row one. So if they start from A, that's row A in your Excel, in your Excel, which I know all my data starts from row A, then row A will have a heading. So row A has a heading. And if row A has a heading, which means the very first row always has a heading. So the way to tell power query that look, row A has a heading is to add to this code and say, comma true. So if I say comma true, it just knows that, okay, that's the way of telling it that row A has a heading. So if I click okay, it's going to try and pull out the table here. This is really what I need to expand. I don't need binary anymore. So I'm removing that. Then now if I expand, you'll see that this is what's inside. And really, I think we should keep name and data or maybe item. Item, I think is better. So I'm going to on select or say, I want data and item. Item tells me a little bit more. The rest I really don't need. Well, let me just, if I cancel this and just show you what you have in here. So if I click on table, so this is really what you have. I have name April. I have a table. I have April. Again, this is the item April, then sheet, then false. I really don't need that. I'm going to kind of close this, come back to this. And then when I expand, I said I want just data and item. And then I say, okay, so it's going to expand. And then I know expand this table again. So it's going to extract all the columns in the table. And then I say, okay, so here we have it. This is consolidated everything in the folder. And all you now need to do is ensure that your data types are correct. So this data type is going to transform or form, make sure that this is text. Then from this cross pay all the way to the end, this should be decimal number. Then I can bring this guy back to just want to move it and moving it all the way to the left. So it's next to the text. So I'm bringing it here, just dragging it here. And here we have April and bring it here. And then I make this guy also text. So that's how you consolidate from a folder. It's pretty straightforward. Yeah. And then make sure you change your data types. That's very key. So consolidate folder. I think that's the name. Let us apply. And then we go to another trick. So I'm just applying the steps. If this was Excel, I could extract everything and take it to Excel immediately. And that's our consolidated data. All right. So that's consolidated folder. Let's see what the next trick is. So the next trick is fuzzy lookup. So we're going to do a lookup without a unique key. So I'll show you the Excel files first. And then we'll see how we're going to do this automatically in power query. So let me open. I have some Excel files. I'm interested in the lookup Excel files for referee and results. So if you're a football fan, you'll understand this data. This is, I have dates here. I have my home team, which is Manu. Yes, I support Manu. I'm sorry for you guys that don't. Anyway, yes, yes, we're in trouble nowadays, but we'll come out of it, you know, can be winning every time. Just, you have to give other people a chance, right? So anyway, our way team is Leicester. So we have home team, our way team, we have our home score and our way score. Now anytime we lose, I'm sure it's the referee's fault. So one thing we would like to have is the name of the referee that really manned this whole matches. We need to know the referee's name. I need to know the referee's name here. So where do I get that data? Well, I have another table called referees, which all this data is from the internet. We downloaded it. Ahmed downloaded it for us. Check Ahmed out. Really cool. He went and downloaded football data. I can't remember what team he supports, but anyway, that's fine. So date, home, away, and referee. So if you look at this data, you have your date, the match happened, home team, away team, and referee. So this is uniquely identified. These three uniquely identifies the data. Now if I was doing VLOOKUP, I mean all I do to get referee in here, if I was doing VLOOKUP, I want referee, referee. I would need to concatenate, if you guys don't concatenate, concatenate these three, have it as another column, then do a VLOOKUP of that to the other sheet and blah, blah, blah, blah, blah, blah, and then get the referee. I'm not going to do that. No, thank you very much. I want to do all this VLOOKUP without a unique reference in power query. So let's see how that works. I'm closing this. Don't save, and I'm closing this guy. And yeah, I can do it here in Excel. You know power query is in Excel, but I want to do it in Power BI. So I'm back here. So look up without unique key. How do we do that? Let's go to the query. Edit query. So we're going to bring in, let's just edit query. I'm going to bring in the data, obviously. So and then we're now going to do some interesting merging. So here I'm going to get data. New source is in Excel. I need to browse. This is the lookup. It's referee data and result data. So let's take the result data and I'll just bring that in. This is my results data. I'll just tick it and say, okay, bring the result data in. Let's call it. This is called results. This is fine. Let's bring in the other data. So get another data. This is my referee data. Right. So this is referee data and result data. So if in Excel, I could be probably managing this data and this data keeps growing. So that's fine. So here I want to combine the referee information. All I need from this is this referee. I just want to add this referee to my results. Yeah. Right. So how do I do that? Let's create a new query. So it's like I need to take results, starting from results, and I need to group. I need to merge. I need to merge my query. So I'm going to create a merge query, but instead of overriding this query, I want to create a new query, merge queries, but as new, as a completely new query. So I'm going to merge query and starting with my results. I want to merge it to my referee's data. And here I'm just going to do a left outer join. All these fields, see these fields here all the way here. I'm merging it to this fields here, all these fields. Yeah. One, two, three, one, two, three. And I'm doing a left outer join, which means this is what I want to join to this one. Right. So anyway, let's see how that works. Let's say, okay, so the selection has matched 790 out of 790. This looks cool, doesn't it? That means all the data here is all here. It's already matched it. So this is VLOOKUP, right? So click OK. And what happens? You have your data that has come in and you have your merge. Let's just call this the, I call it the lookup. We're not done yet, but I'll just call this the lookup. Okay. So I enter lookup. So this is the referee table. I mean, the referee data I brought in, if I expand this, all I need from this, I don't need the data. I have the data. I don't need home. I don't need a way. All I need is the referee's name. So click OK. And see the referee's name comes in. In fact, I can take this guy and take it to the left so that now I have eight home team, away team, referee, and then my data. And these two files are still there. See, these two files are there. They can keep on growing as they like. And then this is my third query, which is my lookup query. Perfect. And I can load this and do all my analysis I like. So that is how you do a lookup even without a reference. It just created a reference for yourself instead of doing all this in Excel. It's far better and more efficient to do it here in Power Query. So I'll apply the steps. As a result, this has applied all of them. So we've done our query. So in fact, all these three, let me create a folder. Let's be a bit neat here, because I needed three things here. So I'm going to create a group. And this group, I'm going to call it the lookup. I'm just going to call it lookup, right? And how to do the lookup in Power Query without, even without a reference, right? Even without a reference. Right. So I click OK. So I create a folder. And I can now take these three guys. I'll just, unfortunately, I can't drag it. Maybe later we can drag. But for now, I just have to right click and say and move to group lookup. Right click and move to group lookup. Right click and move. Come on. Move to lookup. To this, I'm just being organized, right? So that's lookup. So what's our next, what's the next trick? We've done five now. Or is it six? I think we've done six. So number seven, also number seven, create columns from example, Flash Fill. Interesting. Let's show you how it's done in Excel. Let me open something in Excel so you understand what Flash Fill does in Excel. It's such an excellent tool in Excel. Many people don't even have forgotten about it or don't even know about Flash Fill. So look at these names, right? These common names we have. Let's assume I just want the surname. And then I want the first name. So I want the surname and first name. Now, if you're very good in Excel, right, you would do some really fancy formulas. Now, let me just, should I show off my Excel skills? No, not OK. Well, what is the delimiter? There are two delimiters here. What is stopping? How do you know the surname or how do you know the first name? Well, the surname has a comma after it and then has a space. So what you could do, you could search for, let's say you could search for comma within this guy, right? So when you search for it, it says a seven. So if it's seven, that means you have six characters for the surname, right? So if I drag this down a little bit, you see that this is seven, seven, nine, nine, nine. Oh, that's strange, all nine. And then this is seven, right? Now you have seven and nine. What kind of names do we have here? OK, 11 and five, better. So seven. So what we now need to do is come in here and then say, you know what, I want you to extract from the left, extract names from the left. And how many characters are extracting? This is seven, remember? Minus one. And we close our bracket and then you get his name. Right. But this is too much stress. And so what Excel did, which is so cool, you should just type, just type Cabral. So I'm just going to type Cabral. Then I type a second one and that would be like an example. I type Fallon. And guess what? Excel has an idea. It says, hey, I know what this guy's doing. And let me give him an example. OK, I will show him. If he's happy with what he wants, then he should just type Enter. So I just type Enter. I guess what? It just came out. Perfect. So first name, same thing, same trick, Michael, or Michael, sorry, Michael. I'll spell it the other way. Now, another trick, a shortcut for Flash Fill is actually Control E. So I don't need to give it another example. Like a sales teller, look, do you know what, use this guy and just fill it. Control E. So it did Control E and it filled out. So this is Excel. It's quite impressive in Excel, yes? But let's do all these tricks. Let's do the same trick in Power Query. You don't want to do Excel because Excel means you need to do this every month. In Power Query, it is automated. So just know that Excel is good, or Power Query is better for some things. So I'm going to close this. Don't save minimize. And let's do this, creating columns from example Flash Fill inside Power Query. So I go to Edit and Edit Queries. And then now I need to go and look for what file should we use. Let's see. See this. Yeah, we could use this guy, right? Yeah, so let's use this. I'm boring from this. I don't know who used this. Can you remember? Well, there's a trick that would tell you, okay, who is using what? So we don't mess things up. I'll show you that trick later, but I'm going to duplicate this just in case. Duplicate, right? And then I'm going to rename this guy. Let me call him Flash Fill. I can't spell Flash Fill. Okay, so Flash Fill. I need to fill this just like I did in Excel. So how do I do that? Well, there's a wonderful new tool. I can't remember when this was added, but columns from example, column from example. Column from example is a new tool. So I click on the dropdown and I say from all columns or from selection. Well, I've already selected this. This is the example I want to use. So I say from selection. So what it does is just gives me this box here. And inside this box, all I need to do is type. So I just start typing just like Flash Fill. Cabby cabral, right? And then I enter cabral. Enter. When I enter just like Flash Fill, if you remember how we did it in Excel, all I need to do is now say okay. Once I say okay, see it's filled it out. I can call this the surname, right? Then I can still go back and select the same column like this and then say I need another example from selection and then this time around, obviously, I'm doing the surname, right? Oh, the first name. So it's a my, my call and enter. And you can see it's already filled it out. You just needed one example and just say okay. And then this is the first name. Now remember, this is power. I don't really like space. So this is power query. So when your data grows, you never, ever need to do this again. It's just automated. That's why this is more superior than your Excel. So if I drag this to the left, let's just put the names close to the surnames, everything is recorded. And if you look to the right, you see that this is being recorded. And in fact, let's even look at the formula, the M code, you know, power queries language is M. So let's have a look at the M code it used to do this automatically. If I click on this, this is the M code up here not the M code. Let's click on where is that insert text delimiter. So look at the M code it used. Now, if I put on the magnifier, let's just see if you can see that. Let me get my magnifying glass up. So this is the M code looks ugly table dot add column rename columns with the hash, then text after delimiter each text dot after delimiter name comma space. So all that story just see the space, just that space now similar to how we did it in Excel. But that's power query. Then you can actually use this to learn how to write M code. Okay, so that is how we do it. I go to number eight is use the choose column button. What do we mean by that? Let's have a look at it as an extra choose column button. Right. Let's see how that works. Okay, so let's go to query edit query. I don't think I saved the last query. So let's come to home here and just apply any other steps that I did. I think I did some steps for flash fill in the last video. So flash fill. Here we have. So here we want to talk about choosing columns. Now, one big thing when power query was being developed, you could kind of highlight like this and delete. But what about if you had a hundred columns, then you need to kind of highlight a hundred columns and delete. So what they invented, which was cool the first time around was they kind of allowed you to highlight the columns and do something to other columns. So you could right click and say something like remove other columns or you could say on pivot other columns. Yeah. So that was a very cool move. But then sometimes, right, you actually want to remove just this guy and this guy and another one somewhere else. So what you could do instead of doing that is you choose your columns under home as a new button here called choose columns. I'm not too sure when it was added, but choose columns. So if I go to choose columns, I can actually, are you sure you want to insert a step? No, no, no, no, no, let's cancel. So look at this. I'm in a very different step here. So I have to be at the last step. So I was already in a mid step. So I do want to insert any steps. Let's just do it properly. So I come here to choose column. I say choose column. Then you can actually choose which columns you want to keep. I can remove this. I like this guy, I like this guy, I like this guy. And this guy, no, I don't want this guy. I want this one. And I want this one. And I want that one. So you are choosing your columns. And once you've chosen and you say, okay, it's basically written the code here. It says table dot select. Let's see how this looks. So look at what is writing here. It says table dot select columns, reorder column name, department name, title, leave, detail, blah, blah, blah. These are the columns you have chosen. Isn't it? So that's how choose column works. Pretty simple and very useful. All right. So what's next? Let's save this. I did this on Flash Fill, right? I shouldn't have done that. I should have just given it another name. I said choose columns. All right, Flash Fill. I'm going to duplicate you. Duplicate. Then I'm going to call this one choose columns. Choose columns. That's another trick, by the way, duplicating columns. And then I go back to Flash Fill and I rewind. So you can press rewind. I've deleted this. I press rewind, reorder columns, fine. So Flash Fill is fine. This is to choose columns. And then I apply all these nice queries I've done. I'm applying everything up to date. Yes. And then what's our next trick? So our penultimate trick, what's it? It is view relationships between queries. How do you view relationships? We've done so many queries. What's the relationship between them? Let's have a look. So view relationship within queries. How do we do that? So obviously I said view, right? So it should be somewhere in view. So if I go to view, then you can see query dependencies. So click on that and then you have this dialog box. Let me maximize it. This dialog box, I can zoom in a bit so you can see it. So this is a mapping of all the queries you have. So this is one source. If I hover over it, you see this is my source called Consolidate Folder. If you had watched all the videos from the beginning to the end, you would see that we did a Consolidate Folder and this is it here. So this is just the direct link. Then we had, I did a lot of stuff with one file called autoheadings recon. So autoheadings recon, we loaded, we did summarized data with it, we did autoheadings recon, we duplicated it, we did a flash fill with it, we did choose columns with it. This auto recon number two, we did profile with it, we created a table profile. So we use this quite a lot. We can see the mapping of how we used it. What else did we use? Here is lookup. So this was lookup. If you remember the referee stuff, we had results for our matches, we had the referees and then we did a lookup. So you see how nice and neat it is? So if your queries are nice and neat like this, you can know what is depending on what. So if I come and try and delete this, for example, it won't allow me to delete this query because lookup is dependent on this. So if I want to delete this, I must delete this first, right? So that's how you query dependencies. And that's how Power BI and Excel manage all these query dependencies. You can see the mapping. It almost looks like Power Pivot. Yeah, Power Pivot data model. Very cool. Right. What's our last and last tip? Let's see our last tip. So the last tip of the day is consolidate worksheets in Power BI. Consolidate worksheets. Well, we did consolidate in Excel and we found out something. So let me just quickly show you that. So if I go to Excel and you try and consolidate, we go to get data, you're consolidating from a folder or you're consolidating a workbook. Consolidating a workbook and I want to consolidate O2 headings recon, for example, right? So you get this dialog box. Yeah. Now, in this dialog box, I want to consolidate all these sheets. So definitely I'm going to select this. Once I select this, I edit. That's Excel. Let's do the same thing in Power BI. So if I'm in Power BI, I want to consolidate. I go to get data. I'm going to get data from Excel. And then I am going to come here and I want to consolidate based on O2 headings recon, right? Same thing. Exactly the same thing as I just did in Excel. Right. So same look and everything. But when I click on this, you see edit is not activated. Why is that? I think that's a bug. I'm not sure. But I think it's a bug. Look at this. This is the Excel version, right? April. See edit. But if I click on this where all the sheets are housed is the workbook, I can see edit, right? And I can click on edit. But I click on edit, this is Excel. But I click on edit in Excel, it brings up the query, which is fine, brings up Excel query and is ready to move, ready to roll. This is Excel. In Power BI, the same thing I did in Power BI, I can't click on edit. What I can do, though, is I can right click on this and then edit. So this is probably a bug. So this last one was really to just showcase a bug. Click on edit. And once I click on edit, yes, you can see that Power Query is working exactly the same way as I have it in Excel. So this is the Excel version, right? This is the Excel version. And this is the Power Query version, right? So I guess, guys, we are done with the demos. These are all the demos. Let's see what's the next on the next page. So about the sponsors. So all these webinars were sponsored by dbrown consulting. We do training, we do consulting, we do payroll, we have the analyst training hub, we provide tailored training for the modern analysts from zero to AI, we like to say. So provide Power BI training, financial modeling training in Excel and all the other kinds of training. You can check us out at www.dbrownconsulting.net. And if you want a free Excel course, just go to www.officetraininghub.com. We get a free Excel course there. We also do payroll. We do, we have a special software where we can guarantee confidentiality. We also use Power BI to kind of consolidate data and clean up data and automate reports for various clients. So that's what we do. We have various affiliates. We have some of our courses are CPD certified. And of course, some of our courses are also certified by the financial modeling institutes. We do the exams here in Nigeria for national modeling institutes. The new body go to FMinstitute.com. Excellent body that has certifications in financial modeling. We are also Microsoft partners and we're also affiliated with the ATD Association for Talent Development. One of our cool tools we have in Dbrown Consulting is we have a model, we have a methodology for training, which is pre-training or we have to do a pre-training, which is a pre-work, which we do online before you come for a classroom training. And then we have various things that will make us identify whether you're applying those skills. So it's our PTA system, very cool system. You can check it out. You can send us an email to training at Dbrown Consulting for more details for our PTA system, which also has four proprietary reporting systems. So our reporting system is in Power BI, very detailed reporting system. So please contact us. This is our contact details, where Dbrown Consulting, you can call us on 0700 training. If you're calling from outside Nigeria is plus 234 700 training or just email training at dbrownconsulting.net. I'll go to our website at dbrownconsulting.net or you could go online and get some nice free courses and some other paid courses on officetraininghub.com. I'll advise you do the report automation in modern Excel courses and excellent course. I'll teach you a lot of the tricks you've seen here. So thank you very much guys. We've managed to do quite a few queries. I did various stuff, various tricks that Power Query has that could be hiding and we also showed you everything using Power BI. And again, as I said, is exactly the same tool in Excel. Although we just saw a quick bug where Excel seem to have trumped Power BI. Well, I'm sure it's just a tiny bug that Power BI team will find it and correct it. So thank you. I've been David, your host. And this has been Dbrown Consulting. And we hope to see you again soon. And next webinar is the third Thursday of every month. Every third Thursday of the month we do these webinars. This has been 10 things you didn't know Power Query can do, sponsored by Dbrown Consulting and is our Excel and Power BI webinar series, which is held every month on the third Thursday of every month. This is the September 2018 edition. And we look forward to seeing you next month. Thank you everybody and bye-bye.