 So, trick number four, or trick number five, we've done four, trick number five. Just to recap for you guys, the first trick was call up function help and use the M IntelliSense, the new M IntelliSense 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 thing 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 we're going to add a new source. Now, if I was doing this from Power BI to 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 to 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. A little 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, 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. OK, 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 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 are creating your data and all the data that you're extracting are inside tables. If they're inside tables, well, that's fine. On if they all start from row. Really, they all start from row one. So if they start from A, that's a row A 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 we're going to on select or say our own data and item. Item tells me a little bit more. The rest I really don't need. Well, let me just 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. Let me 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's us apply. And then we go to another trick. So I'm just applying the steps. This if this was Excel, I could extract everything and take it to Excel immediately. And that's our consolidated beta. All right. So that's consolidated folder. Let's see what the next trick is.