![]() So anytime we see the word date here, we can get rid of that.Īll right, so we're going to open this filter and get rid of the first part of Fair Home, anything that says amazing, anything that says parameter.Īll the way down to the bottom, we're going to get rid of the word date, and then we're also going to get rid of any blank or that's spaces. So anytime that we have FAI or a maze or a paramet.Īnd, of course, in your data, who knows what kind of junk is up here in these title rows.Īnd also the word date has been moved up to the header. You'll see in the date field, Fair Home Scarecrows, amazing system report and parameter. ![]() And let's just come down here to page two. Now, we need to get rid of all the blank rows.Īnd one of the fields that would be great for this, like if the name is blank, that doesn't mean anything because see, all of these are data records, but the reason code and the date seem to be filled in all of the time. So now we have our headings in row one and the data starts in row two.Īll right, we're going to take those headings and move them up to be the column name. So we're going to come back to Notepad and save this little set of column numbers that I copied out of Excel, Ctrl+C, and Paste, click Okay. Remember, what we would call character 1, Power Query calls character 0, and then character 2 and 5 and 10 and 14 with commas in between. There's a tool tip there that only appears after you click Advanced Options.Īnd that tool tip is very helpful because it shows you that you start at character 0. Positions.Īll right, so we choose positions, and this is where they want to see what the positions are. Here in Power Query, it's Home.Īnd then split column by positions. So now, we have our fields here and we need to split these fields in Excel. So here under Home, Remove Rows, Remove Top Rows, and it asks how many rows to remove.Īnd we say that we want to remove six. So that means that the first six rows are junk, and we're going to get rid of those rows, so we can move the headings up to the first row. But that's okay, we'll be able to fix that later.Īnd you see that your headings are appearing in row seven. Next up, Transform Data, and the column's not wide enough for us to see everything.ĭrag it as far across as you can, but you still can't see state and zip code. I want all of this data to land in one single column for right now. So we need to change to something that's not in the file, for example, a tab. Power Query looks through this data and it detected some commas here in row three, the parameters row, and that's not how this data is separated. ![]() On the Get and Transform data, say From Text/CSV.īrowse through the folder that has all of your files, choose the file, click Import. We have our report, we want to try and clean it.įirst thing, instead of opening the file, we're going to come to the Data tab. Just to get that information, I'm going to go out to Notepad, and paste that in Notepad, because I won't be able to get back to Excel while I'm cleaning this data.Īll right, so here we go. So I needed a simpler formula here to subtract one.Īnd then a TEXTJOIN to join all of those numbers with a comma space in between. One issue with the Get and Transform tools is that they're zero base. ![]() And the reason is in column 58.Īll right, so I just created a nice little list of where each field starts. And see that's column 12.Īnd the ID number is in column 47. So we have to figure out, for these nine columns going across, where they start.Īnd, for me, that was open it in Notepad++, click where the start of the cell is, and then look down here in the bottom. So the first step is going to be harder using the Get and Transform tools.īut after that, everything is going to be dramatically easier. I'm actually going to give kudos to Excel because, in step two, being able to draw the lines where they belong is easier than what we're going to do in this video. The first thing is, when you go and open this in Excel, and we have fixed width. This is just an ugly data set.Īnd cleaning this in Excel would probably take an hour or more. More blank rows between Marjorie and Jasmine. In this one, Marjorie appears on the first row, but not on rows two, three, four. Look at this, 1,208 pages of data.Īnd there's all kinds of problems in this data, like a blank row after every single record. text, and when we look at it in Notepad, you can see that this is just some old, ancient system, writing things out. It was 1,000 pages that were basically printed to a text file. Yeah, I saw a file just like this file last week. Learn Excel from MrExcel Podcast, Episode 2359, Importing Fixed Width Text Files. This is going to save you so much time, it should be illegal to know this trick.
0 Comments
Leave a Reply. |