 So today, what we're going to do is talk about a tool that is in Power BI as well as in Excel, as well as in Azure when you're working directly in Azure. So there's something called data flows. Data flows, get and transform, all of them are the same. In fact, I'm going to open Excel, I'm going to open Power BI, so you can see all of them work. They're pretty similar, pretty the same. There's not much difference between the two. Five things you didn't know Power Query can do. Well, we're going to consolidate multiple sheets, and the nice thing about Power Query is that they've actually improved the interface. The interface is much easier than it used to be before. You had to write a little bit of M code, now you don't really need to. So what we're going to do is let me just get my system screen ready. So you should be able to see my screen right now. And in my screen, let me just pop out Excel for you to have a look. And then this is Power BI. So for those that don't know Power BI, let me start with Excel. So Excel, as we know, is the number one business intelligence tool out there. Everybody in the world uses Excel. Even those that don't like to use Excel and have a beautiful business analytic or visualization tool have one button called export to Excel. And they export their data to Excel when they can't do something elsewhere. Because Excel is extremely versatile. All these cells, every single cell is programmable, right? And you have more than 17 billion cells in a sheet, right? So what are we going to do? The power tools, those power tools, let me show you where they all come from. Now, the version of Excel you're using is very important. So Excel, as you know, we have home, we have insert. So by the way, I'm using Office 365 Pro Plus. How do you know? You go to file, then you go to account. Once you go to file and account, you will see the version you're using up here. Subscription product is what I'm using, Microsoft Office 365 Pro Plus. Now, this is like the latest, latest version of Microsoft Office. So if you're not using 365, then you probably will be using maybe Office 2016, Office 2019, Office 2010, Office 20. I hope you're not using anything below 2010. I mean, even 2010 is too old. So I'm using Office, let me type mine. I'm using Office 365 Pro Plus. I use Office 2019. Okay, you have to specify exactly what you use. Is it Pro Plus 2019? Is it business 2016? Is it? Yeah, because they all do different things. Why that is important is because if you're using 2019, you have all the tools that I'm going to talk about. But if you're using 2019, Professional Plus, right? You have to see this Pro Plus. It's very, very important. Pro Plus. Yeah, Pro Plus. What does Pro Plus do? So if I come to options, for example, say I'm file, if I go to options, you'll see what I mean. Under options, yeah, you go to the add-ins. There are certain add-ins, certain very professional add-ins that you need to make sure you have if you want to use all the Office tools well, right? So for example, if you look up here, I have data streamer, I have inquire, I have power pivot, I have weather, I mean, all sorts of tools. Now, most people will have certain things. So if you go to excel add-ins, once that, you know, I went to excel options, add-ins, can you see the add-ins here? I click on com add-ins. When I go to com add-ins, this com add-ins, I click on go, you will see those power tools here. So down here, you can see I have Microsoft Power Maps for Excel, Microsoft Power Pivot for Excel, Microsoft Power View for Excel, inquire, go to meetings, something, dark studio, these are other extras I've added. But these Microsoft ones, just check your system, go to file, options, add-ins, and then com add-ins and see if you have these ones. If you don't have them, maybe you don't have professional plus. You most likely have, maybe it's business, or maybe you have a user, maybe like student edition or something like that. So please, anytime you're buying Office, make sure you buy professional plus. And I advise you to get your Office 365, because that is the latest of the latest, right? Office 365 is the latest. Now, why do I mean the latest? Because Microsoft updates Office every single month. Every single month, it updates Office every month. Now, once it updates Office, what do I mean by updates? There's so many new things in Excel. Let me give you one. Yesterday. Now, this is yesterday, right? Yesterday a new function was added in Excel, just yesterday, right? And the only people that can see that new function is a very major new function. The only people that can see it are those that are in the Insider program. Now, you see, even if you have Microsoft Office 365 Pro Plus, there's something called Office Insider. So down here, Office Insider. And Office Insider means that any new thing Microsoft brings, they'll give it to you first. And then you use you to experiment, you experiment on it with your system. And everything, once it's stable, they now give everybody else. So in my Insider Plus, there's a new function called Let. I'm not going to talk about it. We're going to do a video about Let very soon. Do a video later about Let. So Let is almost like SQL, right? Almost like a SQL, what people do in SQL. So we'll talk about Let. And Let, if you're in power, power pivot and use DAX, Let is a bit like variables, right? It's a bit like a variable. So in DAX, for those that are very advanced using DAX, you know, you know about variables. If I show you the syntax for Let, you have Let name one name, name value. So you're letting name one, you're kind of defining a variable and then using that variable in a calculation, right? But anyway, so Let's, let's, let's leave that as advanced. So let's jump into what we're talking about. We're talking about power query. Now, if you have Office 365, and you have 2016, or you have Excel, Office 2019, you have power query is on the data. So if you go to data, you will see a section here, a very important section called get and transform data. So get and transform data, you see it down here. This is power query. That's what is called in Excel. Get and transform is power query. If you have 2013 or 2010, you will not have get and transform. What you have is get external data, which is the old school one, right? So get and transform is power query. And that is a super, super powerful, powerful tool. And that's what we're going to work on today. So let's see what we're going to do. So let me show you, let me open some few files. I'm just going to open a few files on my desktop. And you'll see what we are referring to. So I want to consolidate data and various sheets. I want to consolidate data and various sheets. So data, well, sheets, data that's inside various sheets, right? So I'll just open one for you. Let me just say I open raw data. And how do we consolidate it in various, various sheets? Let's see how that works. So if you look at this file, so this file right now has like payroll information you have. So this is your typical data. I have employee number. Let me show you that I have employee number. So you can see that data, employee number, you have name, you have month, you have basic housing transport meal, gross tax pension, all these various data stuff. And then what I want to do is that, that if you look at the sheets, I have the January sheet, February sheet, March sheet, April sheet, all these various sheets, right? So I want to consolidate all these sheets into one. And anytime anyone adds a new sheet to just consolidate it regardless, right? So what do I do? Well, let me close this for now. I'm going to use, let me close this, I'm going to use this sheet here. I'm going to get data. I'm going to connect to that data. So I'm going to connect to data now. Power query can connect to more than almost 200 data, data, data, I mean, 200 different data sources, right? From database, from Azure, from online services, even a website, you can dump a website there to connect to a website, connect to all sorts of data, and through the old data feed, it connects to all sorts of data online. So old data is open data, open data feed, so much data, so many different data sources can be connected through the old data feed. And you can connect directly through an OLDBC, for those database people, you have an OLDBC connection. So, so hundreds and hundreds of data sources can be connected. I'm just connecting to Excel. So from file Excel, very simple. So I'm connecting to Excel. What I'll do is I'm going to navigate, I'm going to navigate to my desktop. That's where I have my file, I say PBI webinar. And these are my files, I'm going to connect to consolidation. And then that file that we just talked about, that 2010. So click on it and we connect to 2010. Right, so you see that connect 2010. Now you notice that all the files are listed, all the sheets are listed. So these ones with the blue, see these ones with blue, blue, blue. You can see them are very tiny. Sorry about that. Let me see if I can navigate, put a navigate or magnifier, magnifier. Let me activate my magnifier for you guys. Yeah. Okay. Maybe, so just, just trust me, tiny, tiny blue. Those are the tables. And then these are the sheets, right? Those are the tables and these are the sheets. So what we need is we're consolidating everything. So we're not just going to pick one at a time. If I pick one, you see it here, see the sample. This is the sample data. What we're going to do is kind of consolidate from the folder directly. So you right click that folder, and you say, that's transform data. That's just transform data. So when you click on the folder and transform data, it's going to open an application. It's separate application, which is Power Query. Remember, Power Query is an application. So it's opening that application, right? So I need to magnify. Let me see if I can use it. I think there's a shortcut for me to use it. Let's see. Okay. It's not going to work as well. Let me just stop it. Let's escape. All right. So let's see. So this is us connecting to, connecting to a folder. Yeah. So anyway, we have your data. So what happens is you have your data there. You have your data. So this is all your data, your name, items, kind. So this is your metadata. This is the metadata of that file, right? So name, items, January, the name of the sheet is a sheet or a table. And if you look at this, we have sheets and tables. Now, there's something called table in Excel. We convert data to a table. And then if you don't convert it, then it just remains a sheet. So what we want are the tables. So it's a good idea to convert to table. I'll show you what that means shortly. So I don't need all these sheets. So I'm kind of deselecting sheets. Yep. So now I'm only left with table, right? So I'm only left with table and we have our table here. So now I can kind of remove all other, I'm going to remove all other columns, remove all the columns here. So I'm just left with this table. And then I'm going to expand the table itself. So when I expand the table, you see that all the list of names, all the list of sheet headings are there, just like we have it. Typically, you should remove this button, use original column name for prefix on ticket and then say, okay. So when you say, okay, it expands it. And this is technically your consolidated table. I mean, this is all the things in your various sheets. So you have your April, your August, these are all the sheets. And this is your table consolidated. It's as simple as that really. And then you load it into Excel, right? So I mean, if you look at the employee number, you see very important thing about data types. So your data types are the next thing you need to check, because all of this are currently ABC is very tiny, but this is ABC123. And that means the data type is any, you should never leave your data type as any. So what do you could do? I mean, they say the most efficient people are the laziest people, right? So they, because they have to look for faster ways to do stuff. So what you normally do is say, hey, this is a text data type, this is a text data type, this is a text data type, this is a number data type, this is a number data type, but you could just use under transform detect data type. So you click on detect data type, it goes through everything. But of course, Jolly went through housing, I need to highlight everything for it to detect for all. So I'm just going to do shortcut Ctrl A. If you click on one Ctrl A, that selects the whole thing. And then I can say detect data type. So it goes and checks for all. And what is done, it is written an M code here. So it's written an M code that's kind of checking everything and saying, hey, what is your data type? Like housing data type is number, employee number, data type is text, name, data type is text. So it's done all of that for you. Sometimes it doesn't work as well. So you better be careful. But I think it's fine for us right now. And once you are done, all we need to do is come to home and close and load. To close and load, let me say I'm going to close and load into Excel. So I'm just loading directly into Excel. Now if you don't want to close and load, you could probably just create a connection. But this is your consolidated data for the entire sheet, every sheet there. So this is where consolidating a sheet. Consolidate workbook. Consolidate sheets. So that's simple, pretty simple. What next do we do? So that's how you consolidate sheets. What do you think about that? Do you use this? So here we're talking about consolidating multiple sheets. So the next thing we're going to do in the next trick we're going to do in Power Query is consolidating workbooks containing multiple sheets. So what we just did before was consolidating sheets in one workbook. What about many workbooks that have many sheets? How do we do that one? Okay, so let's have a look and see. All right. So let's quickly check. How do we do that? Let's go back to Excel. So now this was consolidated sheets in a workbook. Let's go to sheet one. And we're going to now come again. Now we're going to go to data. And we're going to consolidate. Let me see if I can quickly change my resolution. So it's a bit bigger for you guys. All right. So I've changed my resolution. I think it's a little bit better. So now we're going to go to data again. This time, we want to consolidate from a folder. So I want to consolidate a lot of sheets, maybe a lot of these multiple sheets. You know, this was just one sheet, one workbook that had many sheets. Now what I want to do is consolidate many workbooks. So how do I do that? Obviously, I'm not going to connect to a workbook anymore. I'm going to connect to a folder. Right? So I'm going to connect to a folder that has all those many workbooks. How do I connect to a folder? Well, I have to browse and then I'll go to my desktop. I know I dropped it on the desktop. I think I called it RBI webinar and then consolidation. So under consolidation, we have all those files in there. So I click OK. So I'm going to click OK, click OK. So it's going to go look for those data and it's going to open power query, obviously. I'm going to try and open. This is pre-opening of power query. So these are all the data files. I need to combine them. So I'm going to combine and transform the data. So combining and transforming the data means that I want power query to work. So power query is going to open or Excel is going to open the power query application and then get us working. So before it even opens, it's asking us, OK, this is what I saw, right? These are the first file. This is the first file in my sample. If I come here, you can see there's 2010, 2011. These are the workbooks in my folder. So I have a workbook. Remember we did this first, 2010 employee payroll, 2011 approval by 2012. So I want to consolidate all of them. So everything I want, not just one by one. Everything, right? So if you check 2011, for example, so 2011, this is what is inside the 2011 workbook, right? If I click this, you see that this is the data under the 2011 workbook. But I really am not interested in that. I can go to first file, which doesn't really matter which one I pick because I want to do the whole thing. So I'm going to right click this and transform data from the folder itself, right? So I transform data. So I right click the folder and I want to transform the data. So it's kind of processing the query and everything trying to pull up power query editor. So it's pulling up power query. So this is the power query application. As I said, if you have 2013, you don't have this. You need to go download it from Microsoft for free. Even 2010, probably you can download it for free as well. 2016, you have it already. And it's 2019, you also have it already. It's installed. It's already in data. It's under data. You see there. Anyway, so here we're consolidating now because we went to a folder. You have something called helper queries, right? And then under helper queries, you have your transform file from consolidates. This is more like a transforming one sample file. And then this is the query where everything drops. So your consolidation. This is 2010 employee payroll. And you have your sample file here. This sample file is whatever you work on on this sample file. Just take this as maybe the first workbook in that folder. You whatever you do on it, it will replicate for everybody else. Right? So that's it. This was our old query that we did. This old query that we did. And then this is the consolidation that we're trying to now consolidate everything into. So we'll come to the sample file and let's work on the sample file. And on this sample file, all we need, you remember what we need? We only need the tables. We don't need sheets. So it's exactly what we did before. Yeah, we're going to do that on the sample file. So I only need the table and then I only need this. I'm going to right click this and remove other columns. Remember, I'm working on this sample file. This is the sample file. And then I expand the sample file like I did the other time. I remove this. I say, okay. So it now expands, right? So it expands everything. All the data is expanded. And then what I can do, I mean, you have various options, right? What I can do is actually twist all of these things. I could say, Hey, let's do auto detection. Maybe we should auto detect. Now, everything I'm doing here is going to do for all the files. You can see that this consolidation has one funny error. Do you know why? Because of change type. So if you see this sign, this means there's a problem. Remember, we're working on the sample file first. And as we're working on sample file is automatically working on all the files. So looking at this tiny error message is because of this change type. Data changing data type is very important. If you don't change data type properly, things can go bad. So I'm going to cancel that. So you see things are still fine. So I come back here. And what do we mean by change type? You can see ABC 123, ABC 123, ABC 123. It's really, it's not the best format to use. So let me finish my transforms. And after I finish my transforms, then I will now change the data type. So employee number, employee name, month. You have month there. And what else do we have? Yeah, I think those are the things we have. Employee number, employee name and month. Now I could twist this. I could highlight these three, right? Click it. And then I say on pivot other columns, right? On pivot other columns. What that will do is going to take leave these three columns there and then twist everything else so that these now become probably something like headings for our line items for apparel and then value. So I can say this is what I want to have first. And then all my sheets should transform to this. So if I come to consolidate, you see that this is, if you see what I just did, you say I twisted things and everything is fine. The reason I like to twist things like this is so that all the names are here and I can use this column to kind of maybe do almost like a V look up in Excel, right? A V look up in Excel to say, hey, if it's basic, you should call it basic salary. If it's housing, you should call it housing or house allowance. Transport should call it transport allowance. So we can standardize our tool by twisting this here. But anyway, coming here, this is the consolidated one. And you notice the difference. This column has source name. The sample, this is just a sample, no source name. Now consolidated has to list out all the different sources. If I say load more, you will see that all the different sheets are here. Look at them. All the workbooks I mean are here. See the workbooks. But this is quite, the first thing here makes more sense, 2010. I don't need employee payroll. So let me work on this a bit. I can click on this and I can go to transform and say, hey, I want to extract something. I want to extract only the figures, right? So if I want to extract just the figures, what would I do? What can, what demarcates the figures from everything else? If you notice there's a space in between, we call that a delimiter. So we're going to use that to extract this. Or you could just say extract the first four characters. But let's say extract, you see length, first character, you could say first four characters, or you could say last characters range text before delimiter, or text after delimiter, let's use text before delimiter. So the text before the delimiter called space is what we want to extract. So it's going to open a small tool, the space. So I say, hey, the delimiter is space. I just put a space and say, okay, look at what it does. It's going to wipe out everything before the delimiter, right? So it's going to give me the text before the delimiter, which is cool. I can come in here and rename this year. Sorry, can rename this year. And there we have it. So now my data is all nice and clean. I can now check my data types. I can just do a auto data type detection now. I can come to transform and say detect. Oh, no, you know, remember what do we need to do? I need to, I need to basically, let me undo. How do you undo? Undo control Z won't work. You have to come here and delete this step from this. So what I should have done is highlighted everything. So I can click this and do control A. And then I'll say auto data type, detect auto, detect data type. And then I want to change this. This is text, this is text, this is text, this is text, and this is value. Perfect. But this one, I don't want it to be text. I want this to be, I want it to be, I don't want it to be value. I want it to be text. So I need to go change my own data type for this myself, right? So how do I do that? You can go to home, come to data type here and change this to text, right? Replace current, add a new step. Maybe I can add a new step, replace current, replace current is fine. So what is done is the auto data type is modified the auto data type, right? Everything else is fine. It's just modified it a bit. Yeah. All right. So this is it. I can call this consolidated. And then I can come to home, come and close. And I have the option to close and load to close and load. Let me do close and load to slope. Remember, we're in Excel trying to do this. So I'm now bringing my answer here. So how do I do that? I can come here and say, Hey, I'm going to add to data type, data model. This is power pivot. We're not talking about that. I can say only create connection, which means I don't want to bring it into Excel. I just want to connect to it and do my reporting from the connection. But really, let's bring it in and see what it looks like. Let's bring it in. Let's bring it into this existing workbook. Let's put it from A1, right? And let's see how it looks. So it's trying to get data. It has got all the data. Let me close all of this. Let's see how it all looks. So this is our data, guys. 2010, all the way, I have like 7,000 and some empty subsets, about 19,000 rows of data, 2012. All I need to do now is if I go to my folder and I drop a new workbook in there, all I do is right click this and refresh and everything comes in. Yeah, perfect. So this is my Consolidate workbook. Consolidate workbook. No, Consolidate. Yeah, many workbooks, multiple workbooks, multiple workbooks with multiple sheets. I don't know if this can enter. I think this has the limits. We're multiple workbooks. All right, what's our next, what is the next thing we're going to do? Let me switch on to slides. So we've done building the next thing we're going to do is build a reconciliation tool. So what we did is, if I recall, the first thing we did is consolidating multiple sheets, then consolidating workbooks containing multiple sheets. Now we're going to do a recon tool. This is very, very, very useful. We're going to build a whole reconciliation tool. So let's see how that works. So we're going to consolidate multiple, we are going to do a reconciliation. So let me explain the kind of reconciliation I'm going to do. So this is my folder that has data. So if you look at it, I have a recon sheet. So the recon sheet we're going to do is like a payroll recon. Let's assume when you're doing a payroll reconciliation or any reconciliation doesn't have to be payroll. What are you reconciling? Let me open old, for example, right? So let me open old. So look at this old sheet. Let me show you all. So it's coming up here. So this is old. So again, we're using the payroll theme, right? So this is data. We have serial number, employee number, staff name, grade level, designation, start date, promote date, then the actual things, the actual salary figures, right? These are all the salary figures, right? Great. So what are we reconciling? When you're doing a reconciliation, you're just checking something that existed before. Does it now exist? Is this still in this new? So we have an old data and the new data. We're checking whether the data in the old is also equal in the same data in the new. And if it's the same data in the new, it has been reconciled. If it's different, you want to see the difference. What is the difference? So take this guy's salary, for example, 3262500.01. Is that the same salary in the new? Let's call this old. Let me open new. So we see what we're talking about. So if I go to recon, let's say payroll, the new, right? So let's open new and see how do we do this manually? Now remember, even if this thing had 10, maybe like 500,000 rows, it's the same technique. But it has 10 rows of 500,000 rows. We're trying to build a tool. So let's call this, this is new, right? So if this is new, you can see that this figure here, right, is different from, it's not different actually. It's actually the same. So it has reconciled. So this and this is reconciled. Let's see if there's any difference for this guy. 45, let's see three. Let's see. That seems to be very compliant. No difference. I can see 836, 836. This guy has no difference. No, it's not one of the differences. So let's see another person. 275118. Let me see which one is very glaringly different. Let's check. Yeah, I can see this guy down here. So see this Trant Paul. He has 186433. Yeah, this one. And in the old, he had 274330. Now, yes, you can do Excel. You can say equals to this minus this, right? Please don't do this. This is so manual. So what we're going to do is automate the reconciliation process. That's all we want to do, automate that process. So close this, don't save, close this, don't save. And here we're going to do a recon. So we're going to pull in a recon. So how do you do that? So data, we're going to get data. Now, if you remember that whole thing was inside a folder, right? So look at it, new and old. We're going to bring these two things into Excel, right? So we're going to bring it. So guess what? It's in a folder called payroll. So we have to go and look for this folder. In fact, let me copy the path. I'm just going to copy this path, right? And then I'm coming to get data from file, from folder. So I got data from folder, and I'm bringing it in where you just paste the path. That's the quick trick. So we can connect to that folder. So connected to that folder, come here, I do the same combine and transform data, combine and transform, and you will see those two files. So what I advise you do is any time you're building a reconciliation tool, you have the before and the after. Just have the before and after. Many have the before and after. Okay. So let's go, let's just try that again. So come here, from file, from folder. And let's, let me just paste this. And I'm just going to browse. And then on the recon, what I want in the recon is actually payroll, right? It's actually payroll. I want no recon. See payroll. So if I click on okay, so that would be reconciling the folder which has the folder and that works. So click okay. So now I've gone into payroll. And now I need to combine and transform, right? Combine and transform. It's going to combine and transform. By the way, guys, I know we'll be using Excel. Everything I've done here can be done in Power BI. This is the same thing. It's really the same thing. But I'm just using Excel. It's the same thing I can do in Power BI. Power BI needs data. This is how we get the data. Now obviously, you can see it's only clean showing, right? We have parameter, whatever first file. See there are two files, new and old. So we're going to work with the two files. So if I'm working with the two files, I'm not going to work with one. I have to work with a folder. So I'm going to right click this guy and transform data, right? So once I transform, it's going to get the data. Give it a few seconds. It's doing this thing, evaluating the query and opening Power Query. So that too, Power Query. Power Query is opening as Power Query is a different application. Now remember guys, we've been doing many things before. If you are done, if this one you're doing now is just the first thing you're doing, you won't see all this. Remember this was consolidation we did. And then that's how it transformed and stuff. So this one we're doing right now is down here. This is helper queries. This is transform file from payroll. So don't look at this one. We've done this already. This is what we're doing now. Transform file from payroll. These are the helper queries parameter, parameter two, maybe another webinar we'll talk about parameters. This is the sample data, the new. This is the actual transform that is going on. This is almost like a custom. Can you see let's, by the way? Can you see let's? This is, you know, I just showed you that there's a brand new function coming to Excel called let. So many things. Anyway, let's move on. Transform sample. This is the sample. Whatever you do to this sample, we'll do to the two files, right? And what we want to do is do to the two files, isn't it? So we're just going to walk on this sample. So you can look at this is the data. This is the table. And this table, again, there's the kind here is sheets. We don't have, we didn't convert that data to a table. So it's just a sheet. So we're fine. We're just going to walk on this, right? So I can remove everything else, remove other columns. And then I can expand this. So when I expand this, you see this is what it is. Use the original column. Let me even leave this. So you know why we like on ticking this. If I say, okay, you will see that this kind of data dot column, data dot column two, data dot, that data dots, it really doesn't add too much value. That's why we remove that step. So if I close this and click here and I expand, right? So that's why we remove this so that it doesn't put that data dot data dot. We're not interested in that. Anyway, click, okay. So this is our data. We need to clean this up. Remember, if the two files, we're trying to consolidate. Now you have the option of actually just doing one file at a time and then now bringing them in. Maybe that would be better because the two files may not be exactly the same size, right? But all right, but this is the sample anyway. Do you remember? This is a sample. So whatever we do here is going to do for each and every file individually, right? So if you look at this, all these line here, they're kind of a waste. This first row, second row, third row, this is where our heading is on the fourth row. So we need to kind of remove those headings. So we need to kind of come up here. I see home, we remove rows, this first three rows. So click on remove rows and remove top rows. If I click on remove top rows, and for us, the way our data is structured is the top harmony, top three. So we're removing the top three rows, click, okay. Now this row is now the real heading. We need to promote it to become a heading instead of column one, column two, column three, right? Under home, you will see use first row as headers. So it's going to promote, right? So once it promotes it, now really what we can do is we can twist. Remember, whatever we're doing here is also doing for the consolidated. Remember, look up, can you see payroll error, right? Payroll. Can you see payroll? Why is there an error here? If you notice that change type, I told you, always come here, delete that change type. So everything, so this is like programming. Whatever we're doing here is doing for the consolidated, right? Whatever we're doing here. Ignore these two guys. This is what we did before, all right? This is why we're concentrating on payroll. So now transform sample. Let's continue the transformation. What I advise you do is, whenever you are doing this, you tell your people that, look, there are certain columns that uniquely identify a transaction. Look at them, employee number, employee name, prefer employee number actually, and employee name should be the same anyway. Then grade level, this uniquely identifies that person, right? Then we have job designation, uniquely identifies that person, start date, promotion date, all this uniquely identifies them. Now, do I need all that data? Maybe not, but I definitely need employee number. And then probably I need, what else do I need? I think employee number is the key identifier for me. The rest are not as necessary. Maybe employee number and employee name, right? So this and this. So what I should do is, I'm removing those things I don't need. I'm right, clicking this, I'm removing this. I'm leaving this to, let's even assume I don't need these ones, but it may be good to have it, but I'm going to remove them anyway. I'm going to remove all of this, right? So why am I removing that? Because what I want is just employee number and employee name. So these two, and then I'm going to highlight these two, holding my control key or shift key to highlight, right, click, and I'm on pivoting other columns. Where is that? On pivot other columns, right? So you see, I've unpivoted other columns. So we have employee number, staff name, attribute and value. Now attribute and value. What do we do attribute? We can rename attribute, right? Attribute is the line item, p line item. I can rename that. Value is the, well, the value. Now, how do we reconcile? Remember, I'm doing this for all. So all my sheets will now have employee number, staff name, p line item and value. Now, if I come down to payroll, remember, this was sample. I come down to payroll. It now has what? Source name. Remember that source name, new stuff. Employee, this just tells us what sheet or what workbook we're working on. It still has that employee number, staff name, p line item and value. So the trick for reconciliation, for this reconciliation, will be for us to write a code, just write a simple M code that says any value that is in our new will be a positive value and all values that are in our old will be a negative value. So we're going to do that now. Okay. So we're going to do that now. So if you look at this here. So if you look at it, let me just recap. This is our transform. This is a sample of what we're doing for one sheet, one workbook. This is the one that has done for both. So these things here, this column here, you can see is new and old, new and old, which is perfect, right? So we could just rename this as our files, right? Source files, right? And all this, we can even transform the data type, the data name now, we can do the data type transformation. We can just come click on this control A and say, Hey, let's go to home or is it transform? And then we say detect data type. And then what it does is detect all the data types on that. So the trick in reconciliation is you can say that all the new data would be whatever sign they have. But all the old data, we're going to transform the sign, we're going to, if it was positive, we make it negative, if it was negative, we make it positive. That's the key trick. For us to do that trick, we need a new column. We need to insert a column. How do we do that? We're going to insert it based on an example, but we're going to say, okay, anytime you have new, well, in fact, this XLX, XLSX, let's just remove it. It's anything after the dots. We're just going to kind of clean that up a little bit. Okay. So let's extract. Let's just extract based on, again, delimiter. And let's say our delimiter is dot, right? So a delimiter should just be dot. And then we say, okay, before the delimiter, so it removes that and we just have new and old. So that's clean, right? So now we're going to say anytime you have old, it should put a minus sign before it. So in XL, remember, this is not XL. This is power query. So we're going to do a new column. We're going to add a column. We're going to add a column, custom column. And this custom column, we're going to say, this is the real amount, right? This is a real amount. And this amount, we're just going to write an if statement. Let me see if I can expand it. I can't. So I'm just going to say if the file, right? If we click on this, if the file, so I'm just typing it directly, but I don't even need to type it directly. What I can do is I can just double click this, right? Source file, right? I'd say if my source file is equal to, if my source file is equal to new, well, let me say old, actually, if my source file is equal to old, right? My source file is equal to old, then, okay, I have to put old in double quotes, right? Remember that, you remember, if you look up here, guys, you remember that our source file were named it new and old, right? So if my source file is equal to old, then I close my bracket, right? So this is my if, this I write the if, if source file equals old, right? Then now in XL, you would have put a comma, but then you say then here, you say then what you should give me is what? Give me value minus one, or just say minus value or value times minus one. So you say this is equal to or give me value times minus one, right? If not, or else, now you say else in, in, you now have to type else, else give me the value. I can see that. So this is the simple logic. If my source file is equal to this, so my if something, logic something, you know, if you've done our financial modeling training, something, if something, logic something, then do this, else do that, right? So this has some right there, wondering why, but shouldn't be. I think it's called old, old and new. Maybe it's case and stiff. I think old was initial cups. Let me see if it was initial cups. Yeah, it should be fine. To control error, let me see what the error is, value, value times minus one. Let's check what the issue there is. Now value, I hope the data type was 1.2. Yes, it was. So this looks strange. So it's showing the error as this value. So maybe there's too much space here. Let's see. I know that's fine. So if then value, this is a strange data type, data issue. Let me just type it again. Let's say value times one minus one. Let's just delete that. Let me double click value again. If source file is called old, then value times minus one. There. Okay, I'm going to type this whole thing again. That's strange. So it shouldn't still give me an issue with value. So let's delete that. Let's just delete this section again and start that section again. If source file is called old, let me even do the opposite. If source file is called new, because it really shouldn't matter. If source file is called new, then let's just say then value, right? Just say if source file is called new, then value. No, I won't put then. If source goes to new, then value, else value times minus one. It's still giving me a funny error, which is so strange. So what I can do is let me show you another technique. Let me just delete that. Cancel. Yes, let's just do that again. Right. So what I can do, let me do it the old-fashioned way. I'm going to create a custom column. Let's break it one at a time. So what I would do is I would duplicate. It should work really. There's no reason why. Something's up with my system, custom column. Let's just try it again. Let's try it again. So if my source file is equal to new and now close my bracket, then give me the value else, give me the value times minus one. Right. So this should work. Let's just say okay. So now saying if it goes to new, give me the value. You know what we said? The guy gave me minus one. So let's check. If source file, I'm going to check my formula again. Extract data hard column. Let me click on this and check it. If source file is equal to new, then value. So this new, we have to, you know, it's case sensitive. So I guess we need to put initial capital letter. The guy is very, very sensitive. Let's just see. Can you see that? So guys, very, very careful. You mean the spelling? It has to be capital letters. We spell it exactly the same way, properly. So anyway, that's good. So this new, now all the old is going to have negatives. Right. Check down, down, down. You see the old have negatives. Why do we do that? You're going to see it very soon. Why we have to do that? So now we've done that recon. We don't need, we don't need this value line anymore. We need the amount line. So I'm going to right click and remove. Then this is our data. So this is our data here. And we're going to go to a group buy. We're going to now group buy. And that group buys now was going to really pull everything out for us. So group buy, we go to home, and then our transform. Then we group buy, we click on group buy. And what do we do with group buy? Wait a minute, this. So we're going to do an advanced group buy. We're going to say, do you know what? I want to group buy source file. Well, I'm going to group by employee number. I'm going to add, because I want to want to see employee name. But really, if I just group by employee number, I can do like almost a VLOOKUP and bring the name later. But anyway, let's just do staff name. And then I'm going to group by attribute. So attribute, which was now renamed as our pay line item. So I'm saying, hey, look at all of these things together. It's almost like concatenate. Concatenate this. And then once you concatenate it, then I want you to add a new column. The new column will be, let's say, recon. Let's just say the actual reconciliation, recon amount on difference. Let's just say recon difference. Let's say we're going to sum this our column that we have called. Okay, we remember we supposed to call it the amount. We didn't call it, we didn't rename it amount, but custom. So once it sums it, guess what? 10 plus minus 10 to be zero. That's what we want to do. So let's just see what comes up after that. So if you look at it, we have zero, zero, zero, zero. All these zero means they've reconciled. They've reconciled. And if I now filter this out and I filter out the zeros, that means I'm not interested in the ones that have reconciled. I'm interested in the ones that haven't. And where you're left with is the ones that haven't reconciled. And that is your reconciliation. Really, those are your reconciliation differences. So some of these values are supposed to be values, but I think they are anyway. But this, so you have employee number, employee name and everything. And this is your reconciled. What would be nice to check is look, was there an instruction for this? How did this guy's salary change? Did they send an instruction? We could actually add that to it, right? We could actually come here and add a new source and say, hey, new source, give me a file. I need an Excel file. And the Excel file, if I go to my desktop, you will see that there is under the recon, we have instructions. And this is the, let's just say, call these new instructions. Our advice, you change this to new instructions. And under new instructions, you would see that we're trying to see, okay, was there an instruction for this? So this is how you do a full reconciliation, looking for, so we'll just edit this directly. We just right click it, transform this data. And then we're going to merge it to this. You guess we're going to merge it to this so that you will see whether there was an instruction for us. Simply, we can just click on this, remove null and say, okay. And then promote headers, just the same thing we did before. And we can right click this and twist and all that. You remember all we needed was employee number, employee name. Maybe we can even leave all these guys here, right? Yeah, we don't really need that. We can just, we can just say, okay, do you know what? We're going to merge. Let's just merge straight. We don't really need any, any, any stuff. I'm going to merge instructions to payroll. So it depends on where you want to start from. If you start from instructions, you're saying, hey, instructions, and then payroll, but let's start from payroll. And then we're going to merge with instructions, right? So how do we do that? We come to merge queries, merge as new. Let's not spoil what we have. Let's merge as new. So let's leave what we have there as payroll and instructions and say, hey, take payroll and take instructions and use employee numbers, right? To merge the two. This is called a left out to join, which means you're taking this and taking this and everything else here is kind of joining this one here, right? So we say, okay, and let's see what that brings up. So what that brings up is going to bring a table to the right. And this table to the right are all the things that are linked to these employee numbers here. So if I click on this, right, I can just click everything and say, just give me the instructions. And remember, we didn't have grade grade level. So we can bring in grade level. Why not? Yeah. So, and you remove this and say, okay, and what happens? It now brings those values there. So this guy was in grade level eight. And this was the instruction removed from payroll, da, da, da, da. And then if there was no instruction, of course, you're not going to see anything there. And then what we can do is now load this into Excel, right? Close and load and guess what? That's us done our recon, right? We finished our reconciliation and it's going to load. This is quite a long case, but this you've automated recon. So all you ever need to do now is drop new and old folder, new and old data into a folder and play. And it just does your reconciliation for you. And anything that didn't have a recon actually probably put some things that didn't have any instruction. If I didn't have any instruction, for example, let me just click on recon, right? So let me come here. I click on recon payroll. No, instruction August. So let me pretend I'm not give people instructions. So let's say, for example, these two people didn't have any instructions, right? So I come here and I refresh. So he's going to go and do the whole thing again. And guess what? See, these guys didn't have instructions. Why did you remove his salary? People go up in arms, especially during this crisis. I mean, we need money. So guys, what do you think about that? That was a long one. But how did that make sense? You're automating recon reconciliation, any reconciliation to work with this technique. Next, move to the next one. We're going to consolidate PDFs. How do you do that? So you do that by looking at the data. Let me go to the data. Let's say these are PDF data. So I just quickly downloaded this and these are stock exchange data. Any PDF would work. Any PDF that has tables inside the PDF, what we're going to tell Power Query to do is one Power Query to go and extract those PDF files. So look at it. This is daily office list of bond prices on Nigerian stock exchange. So this is already in a table form and it's inside a PDF. And you don't want to start copying and pasting, copying and pasting. That is ridiculously slow. So what you can do is tell Power, I mean Power Query to go into this file and pull out all this information for you and into Excel, clean it up and then use it for your analysis. So that's what we're going to do very quickly. So how do we do that? So let's say we're going to use one sheet. Let's console it. Let's pull out this data. Let's check. So you come to get data. How do you get data? Well, if you check it, you say from file, where's PDF? Where's PDF? Where's PDF? PDF, PDF, PDF. This looks like PDF, doesn't it? No, it's not. No PDF, no PDF. So sorry, bad news, guys. There's no extraction of PDF in Power Query in Excel. No, because Power Query in Excel is not as advanced as Power Query in Power BI. So there are certain things in Power Query that have been added to Power BI that have not been added to Excel. So remember, good old Power BI, everything we've done up to now can be done in Power BI. In fact, it can just copy and paste the code and it will work. But Power Query in Power BI is more advanced than Power Query in Excel. So get data. This is exactly almost similar to Excel, right? But it has more features. So if I click on more, so I click on more. So what we can do, guys, is you download Power BI Desktop for free, do your consolidation on PDF extract, and then you can take it to Excel. So look at it. See all Excel currency PDF. Hopefully this will come to Power Office 365 soon. Hopefully, I have to ask the guys at Microsoft to hopefully get this into Excel's Power Query. So if I click on PDF and connect, I now need to connect to the PDF file and go to Desktop and then this is this. And let's just minimize this. So PDF data, that's this, right? So PDF data, let's consolidate one. So it's going to go in and parse the PDF and kind of pull out information from the PDF and check to detect tables. So tables have a certain structure. So it's going through the PDF to look for tables. And guess what? It found tables. See, it found this table. It found this table. It found this table. It found this table. It found this table. And like that, right? So what you can do is just click on this again, just the same way we did transform data, exactly the same steps we did. It's just that the file right now is a PDF. Now Power BI will also open Power Query, which is a different application, isn't it? And it's a different application. We're interested in tables. So we're going to come to kind. And we're going to say, hey, we're not interested in pages. We're interested in tables. These are our tables. These are the things we want to consolidate to right click. We remove all the columns, right? I remove all the columns. And then we expand. And when we expand, as I said, we remove this button here and we say, okay, right? Now we have a table. What we need to do is maybe the headings of this bond description and stop this of correct heading. The first row, really, we don't need it. So you can either remove the null itself, because we also, if you check to the next table, null on the first row. So null on the first row. So we really don't need nulls, right? Nulls, we don't need nulls. So nulls identifies the first row. Or you could basically say, hey, just remove the first row. So we can just say, hey, remove nulls, right? We're saying remove nulls. And then we can say, hey, pull this up. So how do we pull it up? We say use first row columns, where are we? Use first row columns here, right? Use first row columns. So many ways to do that. We could even click here and say use first row columns, so many places. So once we do that, now remember, this is table one. To scroll, you see table two also has a heading. See table three, somewhere down here also has a heading. So we also need to say, hey, remove anything that has symbol. Now, by the way, when you want to do that, go to a column that doesn't have too many things, like this first one. Let's say, oh, they all have many things, but bond description, right? See that? So I say, hey, remove bond description. So this is the only bond description. So the other tables will just kind of join everything together. And what you can do next is come to home or transform and say, hey, let's detect data type or detect data type, but let's highlight everything first, right? So control A and then detect data type. And you see it's now one, two, three, ABC and stuff. And really, that's it. You're done. So this is your data one, right? And that's it. So what do you do with the data in Power BI where you can just close and apply. And that now comes into your data model, right? It comes into your data model as a data. And your data model is there. And Excel can now connect to Power Query, I mean, Power BI as a data source. And then that way can now bring it into Excel. But that's another story. So if you've done this, that means you can consolidate the whole thing. Well, I guess, well, I'm not going to do that. I'll just show you one last thing which we had in Power Query, which is a bit of sorting. But let's see. So if instead of coming to what's it called a PDF, we can actually connect to a folder. So you can connect to a folder that has all the PDFs, right? So if I click on more, I connect instead of connecting to just one PDF, we connect to a folder, we connect. If I connect to a folder, I then I browse, I go to the desktop. Remember, it's inside our webinar, we have a PDF folder, I would say, okay. So remember, this had all the PDFs inside. So you can imagine, you have all the data for the last one here from the Nigerian Stock Exchange, dump all of them there and say, okay. And this thing now goes to that folder. And we're going to be now, remember this is data one, data two, data three, I'm going to click here, combine and transform. And then it's going into that folder. But it will know that, hey, what's inside that folder are PDF files. So it's going to go and parse that PDF file and pull out all the tables. Remember, this is a sample, this is first file, all the files, you have a long list of all your files. But now we need to come to here and say transform. And remember, when you go to the folder and transform, it's going to say it's going to create a sample. And then you're going to be transforming that sample and it's to be like doing whatever transforms you're doing to the sample, if you do that for all the individual files. So this is Power Query. Remember, you see, you have a transform for the sample, this is your sample data. And then this is the whole PDF data. This is what we did before, right? So you're transform, you come to the transform. Remember, we only need tables. So we remove page, we this table only need this column, we remove the rest. And then we expand this column. And then we say, Hey, I don't want this. So remember, whatever we're doing to one is doing to all remember what we did the same step we did. And now we said, it's okay. Then we promoted our headers, use first row as headers. So this is our first row. And then we said, Hey, anywhere else that has symbol, let's remove it. Let's see if symbol is at the bottom. It's not there. Is it there symbol symbol SSS symbol? Yeah, click, right. So let me show you something else. This was one of our other tricks. How do you let's assume you want to sort this alphabetically? How do you even this heading? So you have bonds as would be then s then n. You can actually sort all your headers alphabetically. So to do that, you come to home, you come to home, you click on home. Then there's one new thing here called choose columns. And then when you have choose columns, you come on that choose columns. So various choose columns here, there's a sort thing here, they just added. So if you click on a to z, right, you say sort by name. And then this whole thing gets sorted. Maybe this doesn't make sense to you. But really, it's just a nice trick, right? When you say, Okay, it has sorted all your columns. Now, let me undo that because maybe we don't need that, right? But that's a nice trick. But what this has done guys, is down here, can you see the error? You see that change type always always a problem that chain type. So don't don't don't do that chain type thing. When you remove that chain type, then you can come here and actually change the type. So your source name, this will become your files. Yeah. And then what I suggest is you remove all those pdf.pdf. Well, you can leave it doesn't matter. Then what do you do? You kind of detect your data type. So you click on this control a and tell it to detect on the solution to come here and detect data type, where we detect data type. So it detects all the data type. And then guess what this is your console a data. This one is your sample. So if you want something done on everybody, you do it here. If you want something done on the sample, before it becomes everybody, you do it here. And now your data is done. You come to home, you close and load, close and apply. And this has been added to your data model. And now you're ready to do analysis on the stock exchange. When you get the new pdf from the stock exchange, you dump it into the folder, come here, refresh, everything is done. And you're ready to work. So productivity is significantly improved when you walk with Power BI, Excel, and especially Power Query.