PL-300 – Section 14: Part 2 – Getting Multiple Files Part 2

115. Appending Three or More Queries Together + Resolving a Problem with Data Types

Now, let’s get in a third query.

So, I’m going to now get my March data back in. So, I’m going to March and drag that in. So, there we go. And now, let’s delete my pended query and now I want to have multiple queries appended. So, append queries is new. Notice that I’m not off of that if I’m not actually on a specific query. So, if I create a blank query and then just delete it, I’m not looking at any particular query. I can’t click append query. So, just click on one query if you’ve got, that is it proper. So, append queries is new and I’m going to say three or more tables. And this is fairly simple, straightforward. Say which ones you want and say which order you would like them in, and if you make a mistake you can click the X button, as well.

So, that now puts all three of these queries into one. So, if I call this the total, or overall, and close and apply, you can see we’re making all of these connections. I’ll delete the previous visualisation, and here we have the grand total. So, if I have a table, and put in all my dates, actually, I’ll make it a matrix table, so let’s just change. That’s hard to hitch as a matrix table, and to have dates going across in rows and I have these type going down in sub-columns and the sub-type going down in rows, and the prices going down in values, and I want to change it. So, that it says sum and I can’t at the moment. So, let’s just draw down. And there we can see we’ve got January and February and March’s figures in there.

Now, why can’t I have out as a sum? It must be the wrong sort of data type. So, let’s just go back in, edit the query, see what data type we’ve got. And you can see that it is an ABC, it’s not being interpreted as a number. So, let’s change this to a decimal number. Nope, that’s not going to work because you can see all the errors. So, what we need to do is replace values. We’ll get rid of the pound sign, replace it with nothing, and now we should be able to change this to a decimal number, close and apply. And now we can change this to a sum. There we go, that’s looking better.

Now, the pound sign is the British unit of currency, you might not have that on your keyboard. So, if you’re following along and don’t have a pound sign on your keyboard, then here’s how to get to it. Just go to total, go to your source, click on any of these pounds, highlight the pound sign and go to copy. And then now you can replace the value with your paste, CTRL + V. So, we’re getting examples of how we can cope with data that we weren’t expecting to be in the wrong format. But we have now got it into the right format. We have combined all three of these files together using something called an append query. And the good thing about this is if we, later on, have another month’s worth, so here we have April. Drag that in.

So, here we have another query. All I’ve got to do to my previous query is change the source. So, if I change the source and now April is added, you can see it’s included there, close and apply. And once it’s applied April comes onto this matrix, as well. So, I’ve not had to change anything in my reports. I’ve not had to change any of the macros steps save one, the source data. You might be going, “Well, that’s great, “but I don’t want to see April and February and January and March.” That’s fine, you can hide it. So, now all of these are hidden, we’re left with just the one query.

So append, append is useful for getting two or more queries together in the same query. So, we’ve appended four different queries together. Just one small thing, what if they didn’t exactly have the same heading? So, let’s change this heading to Out Money. What happens to our total? Well, you can see, we now have four columns. So, we have Out and Out Money. Out is being used in January, February and April, and Out Money is being used in March. At this stage, what you can do, is use some more advanced end formulas if you need to. Or, so that it can be combined into one column. We’ll have a look at that a bit later.

Or if you could go back to your source data, or the query, if that wasn’t reliable enough, if you weren’t able to go back to your source data, and change the heading. And then we can have a combined version.

So, you needn’t fear too much if the headings don’t exactly match. Unlike a Union and SQL server, the append query will add as many columns that is needed to have all of the available data. So, that is the Append Query.

116. Combine Files (Getting Information from a Table)

Now, combining queries together is great but do you have to really load each individual month together so you can use the Append Queries. And you might not be too surprised to find out the answer is, “No, you don’t.” So, I’m also going to do, again, I’m going to move March to April out of this folder into a spare folder. So, we just have January and February. Instead of loading Excel, instead of loading Text.CSV I’m going to click on the More. And the fifth item down is a folder. This imports information about your folder. I’m going to click Connect and it wants your folder path.

Now, for you click on Browse, it’s not the best way to browse through your folder structure, it’s just not something I like. I’ve done it to get to that but what I generally do is go to Microsoft Explorer and copy the half from there and paste it into here. Just a reminder, currently this contains two files. So, I click OK and the computer then says, “Oh, there are two files.” And it gives the name, it gives the extension. That’s the bit after the last dot. Date accessed, Date modified, Date created, Attributes. Those are things like read-only, archive, and the entire folder path.

Now, I can combine and transform data and in fact I will just to show you what happens. The computer then imports all of those datas together, all of those data files and makes it into one big query. And you can see there’s all these sampler files these helper queries as well. Sample file, transform file, and so forth that it uses. And you can see at the end of the day we’ve got a source name of the file. The date, subtype, and out, as we have previously. What I’m going to do is I’m going to delete all of those. I’ll also delete these two groups as well and do this again, but instead of combining them I’m just going to have to have the raw data as it was. And you will see why in a few minutes because the computer does a lot of steps to combine those and, maybe, one or two of those steps just quite a bit of tweaking for what you want. So, I’m just going to transform the data rather than combine and transform. Here we have our data together. I’ve got the name, extension, date accessed, date modified, everything that we’ve just seen. We’ve got Attributes as a record so we can expand that if you’d still wish and you can see all of the various attributes which are available. If I just expand the size, the kind, whether it is hidden, you can see the sort of information that you can get.

Now, the content, the actual data is in a binary. So, we’ve a record over here, but this is a binary. And what we can do is we can combine the files by clicking on this double down arrow icon, either of them. If I do that it now opens up all of these files. You can see it’s saying, “Well, what’s your sample file?” And here are the data type detections because it’s a CSV it’s given me delimiters before and I can even say if there are any files that would give you errors, skip those. Here we have the end data, Source Name, Date, Subtype, and Out, but we’ve only got the source name. Remember we started off with Name, Extension, Date Accessed, and so forth. Suppose you wanted the folder path because this process is recursive. So, it goes through this folder and then goes through additional folders.

So, maybe, I’ve got some folder called January from Finance and I’ve got another folder Everything from IT, let’s say. And that’s where these folders were. If I go back and I refresh this you’ll see that the folder path now contains January from Finance, February from IT, but when we get through to the end result it doesn’t, suppose I really needed to have that folder name. Okay, no problem, what I can do is go back over the steps that it was doing, and you’ll notice one of those steps is Removed Other Folders. So, it was doing the, other columns, it was doing the Remove Other Columns here. So, what I’m going to do is going to the settings and I’m going to put back the folder path. Click OK, so now the folder path is part of this and when I go all the way to the end, the folder path is still part of this.

So, what I’m going to do now is I’m going to split this column by the delimiter and I’m just going to split the most right of delimiter.

Obviously, that just gets rid of the last slash that was there. So, I don’t want this, I’m going to remove this column. You’ll notice I’m using the right hand mouse button. There are plenty of these activities mostly in the Home tab that’s up there on the right hand column. And now I’m going to transform this again. I’m going to split the column again by the back slash. Again, I just want the right hand most delimiter. I’m going to get rid of everything else. And that’s what I want, that is my sub folder. I can see the February information is IT. January is from finals. And the great thing is because this is a macro, because this is a series of actions, if I add more information to it, to the source data, I just need to click on Refresh and it gets added into my final thing. How much time might you be saving on this? Again, I’ll just add one more, so, maybe, April was also from IT. Refresh and instantly we get the information.

Now, we can hit Close and Apply. I want a matrix, I want a matrix with the sub folder in the rows, and the data, the columns, just to drill down, and the Out and the values and you’ve also got the same problem as I did previously. So, I’ve got to edit the query. I’ve got to replace the pound sign with blank and then transform this into decimal numbers. Click Save and then Apply. And I can now change this to a sum but now if I had new data, suppose I had new data in May, in June, all of these macros would automatically be done. I only have to set it up once and I get the information here. Combined files though are useful when done in conjunction with New Source, More, and Border.

Now, I’ve been using CSV files. You don’t have to use CSV files. You can be using Excel files, you can be using other types of files, but the important thing is you can get a folder listing of any particular folder, and you can do this from Excel as well. This isn’t specifically a Power BI thing. You can do this in Excel. And within just a few seconds really, you get a listing of all of the information that’s there. And then if you want, you could combine the relevant files together. That is Combine Files, getting information from a folder and combining it into one query.

• Category: Uncategorized