1 00:00:02,710 --> 00:00:09,450 In the old Excel when we wanted to import data from a text file, the interface looked like this. After power 2 00:00:09,460 --> 00:00:13,270 query was integrated as a part of Excel with get and transform 3 00:00:13,480 --> 00:00:18,570 when we want to import data, we automatically get to power query. 4 00:00:18,580 --> 00:00:22,540 This way we have way more options than we had before. 5 00:00:22,570 --> 00:00:24,670 Let's import a text file together. 6 00:00:24,670 --> 00:00:27,900 First off let's take a look at the data we're importing. 7 00:00:27,940 --> 00:00:29,710 The file is sitting on my desktop. 8 00:00:29,710 --> 00:00:31,980 It has extension .txt 9 00:00:32,020 --> 00:00:37,070 It doesn't matter if the file you're going to be importing has .DAT or .CSV 10 00:00:37,180 --> 00:00:39,140 You need to follow the same steps. 11 00:00:39,220 --> 00:00:43,140 Let's just open this with notepad to see what's inside. 12 00:00:43,210 --> 00:00:47,320 So you can double click it or right mouse click and open with notepad. 13 00:00:47,320 --> 00:00:55,420 Here we have our column headers. The information we have in this file is sales information, quantity information, 14 00:00:55,720 --> 00:01:00,050 for different articles by sales documents and company name. 15 00:01:00,070 --> 00:01:04,569 The information itself is split with a comma. 16 00:01:04,569 --> 00:01:06,630 The first one here is company name. 17 00:01:06,670 --> 00:01:09,000 Then this must be the sales document. 18 00:01:09,010 --> 00:01:16,000 Then we have the date, article code, reject information, quantity, and sales in USD. 19 00:01:16,000 --> 00:01:22,840 Notice that we also have empty lines in the middle of the data set and also some lines where we have 20 00:01:22,840 --> 00:01:25,750 no information, just information on the company name. 21 00:01:26,050 --> 00:01:29,920 Let's close this and import this data into Excel. 22 00:01:30,070 --> 00:01:35,940 Open the start file for this section and go to the import tab. To import data into your workbook, 23 00:01:36,010 --> 00:01:40,490 go to the Data tab, in the Get and Transform data section 24 00:01:40,520 --> 00:01:47,470 notice you have "From Text/CSV". If you have another version of Excel, this might be called something different 25 00:01:47,800 --> 00:01:50,830 and you might find it under another dropdown. 26 00:01:51,100 --> 00:01:55,540 If you have Office 365, you're going to see "From Text/CSV". 27 00:01:55,630 --> 00:02:03,490 Just click on it. Then select your file, find the right drive, click on it and click on import. 28 00:02:03,490 --> 00:02:11,560 The moment you click on import the power query editor is opened up. And what it tries to do is one, it 29 00:02:11,560 --> 00:02:16,810 tries to figure out the right delimiter so that it knows how the Column should be split. 30 00:02:16,930 --> 00:02:20,520 And in this case it's found correctly, it's a comma. 31 00:02:20,710 --> 00:02:26,980 If it didn't find it correctly, you can click on this dropdown, select the right one or click on custom 32 00:02:27,070 --> 00:02:29,740 and add your delimiter to here. 33 00:02:29,740 --> 00:02:31,600 But in our case it's a comma. 34 00:02:31,630 --> 00:02:33,350 So we're just going to go with that. 35 00:02:33,370 --> 00:02:41,150 The second thing is that it tried to guess the data types based on the first two hundred rows of data. 36 00:02:41,270 --> 00:02:44,650 We can change this and base it on the entire dataset. 37 00:02:44,770 --> 00:02:49,720 But in our case, first two hundred rows is the same as the next 200 rows. 38 00:02:49,720 --> 00:02:55,570 So it doesn't really matter. Now if you're happy with what you see here and you don't need to make any 39 00:02:55,660 --> 00:02:59,940 other adjustments, you can directly click on load. 40 00:02:59,950 --> 00:03:07,560 What this does is it creates an Excel table on a new worksheet with this transformed result. 41 00:03:07,570 --> 00:03:13,150 You can also click on this dropdown and select "load to" and then select where you want to load it. 42 00:03:13,150 --> 00:03:19,180 If you want to load it on the existing sheet, you can go to "load to" and select a cell on the existing sheet 43 00:03:19,450 --> 00:03:25,660 Or, you can click on transform data and perform more operations on this. 44 00:03:25,660 --> 00:03:33,730 So I'm just going to show you what happens if we just click load directly. Notice a new sheet was created 45 00:03:33,730 --> 00:03:34,580 here. 46 00:03:34,810 --> 00:03:37,150 We have a new table. 47 00:03:37,150 --> 00:03:44,590 This table has a name which looks like our query name which is right here. And here under queries and 48 00:03:44,590 --> 00:03:47,680 connections, we get the list of our query. 49 00:03:47,680 --> 00:03:55,390 Now if for some reason you close this, you can always open it by going back to the Data tab and clicking 50 00:03:55,390 --> 00:03:57,460 on queries and connections. 51 00:03:57,460 --> 00:04:01,900 This shows you the full list of connections you have in place. 52 00:04:01,900 --> 00:04:09,460 The moment we imported this text file we've created a connection to it. Wich means that any time the 53 00:04:09,460 --> 00:04:17,529 information in that text file is updated, we can right mouse click and refresh this and the data here 54 00:04:17,560 --> 00:04:18,950 will be updated. 55 00:04:19,180 --> 00:04:21,250 So source is the text file. 56 00:04:21,250 --> 00:04:23,050 This is the destination. 57 00:04:23,050 --> 00:04:25,050 Anything changes in source. 58 00:04:25,120 --> 00:04:33,890 It's going to get reflected in destination after you press refresh. Now if at any point in time you want to 59 00:04:33,890 --> 00:04:36,730 go and update the cleaning process. 60 00:04:36,740 --> 00:04:39,110 Because right now we haven't done any cleaning. 61 00:04:39,110 --> 00:04:40,630 We just selected the file. 62 00:04:40,640 --> 00:04:43,280 We clicked on next and we just imported the data. 63 00:04:43,700 --> 00:04:48,910 But let's say now I decide, okay I want to remove these empty rows here. 64 00:04:48,920 --> 00:04:56,570 I also want to remove anything that just has the company name and no sales information. To adjust this 65 00:04:56,570 --> 00:04:57,560 further, 66 00:04:57,560 --> 00:05:04,820 I can go and edit my query by double clicking this query and that's going to open up power query again. 67 00:05:05,090 --> 00:05:11,610 But it's going to take me directly to the power query editor where I can do more cleanups. 68 00:05:11,670 --> 00:05:14,000 So I'll take you back here in a second. 69 00:05:14,000 --> 00:05:16,800 Let's just close this again and go back to our file. 70 00:05:17,060 --> 00:05:19,900 Now let's say this was just a one time thing. 71 00:05:19,930 --> 00:05:22,210 You just needed to import this data once. 72 00:05:22,280 --> 00:05:25,100 You don't want to have a connection to that text file. 73 00:05:25,100 --> 00:05:26,450 You want to remove the connection. 74 00:05:26,450 --> 00:05:32,960 Just keep the data. What you can do is to delete the query. Just right mouse click and delete this 75 00:05:32,960 --> 00:05:33,720 query. 76 00:05:33,800 --> 00:05:35,780 It asks you: Do you really want to delete it? 77 00:05:35,790 --> 00:05:37,050 We're going to delete. 78 00:05:37,100 --> 00:05:39,290 Now this is not connected to anything. 79 00:05:39,290 --> 00:05:43,440 If we right mouse click, we can't refresh this anymore. 80 00:05:43,690 --> 00:05:47,600 So now let's go and remove this sheet. 81 00:05:47,640 --> 00:05:54,620 Let's go back and import our data set but transform it before we load it into our worksheet. 82 00:05:54,620 --> 00:06:00,800 So under data, go back to from text/CSV, select a file, click on import. 83 00:06:00,930 --> 00:06:05,340 And now don't click on load but go to transform. 84 00:06:05,360 --> 00:06:11,840 This is something I also recommend that whenever you're importing data files, transform it before you 85 00:06:11,840 --> 00:06:12,620 load it. 86 00:06:12,710 --> 00:06:14,680 This gives you a better feeling 87 00:06:14,750 --> 00:06:17,960 If Excel has picked up the correct data types or not. 88 00:06:21,770 --> 00:06:23,680 So click on transform 89 00:06:23,680 --> 00:06:27,020 and this is going to take us to the power query editor. 90 00:06:27,040 --> 00:06:31,120 Here are some of the steps that you need to do right at the beginning. 91 00:06:31,120 --> 00:06:38,200 Number one, give it a name that you'll like because that name is going to end up to be the name of your 92 00:06:38,200 --> 00:06:39,780 final table. 93 00:06:39,850 --> 00:06:44,390 I'll call this "ImportedSalesData", press enter. 94 00:06:44,540 --> 00:06:51,610 Now notice that there are some steps that have automatically been applied by power query for you. 95 00:06:51,700 --> 00:06:54,000 So right here was the source. 96 00:06:54,100 --> 00:06:59,260 You can click on each of the steps to see how the data looked at that stage. 97 00:06:59,290 --> 00:07:05,940 When I click on source notice that the headers are in separate cells. In the next step, 98 00:07:05,950 --> 00:07:09,100 I can see that it promoted the headers 99 00:07:09,100 --> 00:07:17,020 and after that it changed the data type. Because if you go back here notice that article code is ABC 100 00:07:17,140 --> 00:07:19,480 which means it's not a number. 101 00:07:19,540 --> 00:07:22,980 It's a text data type. Date was also ABC. 102 00:07:22,980 --> 00:07:26,710 It's seen as text and not a date. In the next step, 103 00:07:26,710 --> 00:07:29,720 Excel did all of this automatically for you. 104 00:07:29,770 --> 00:07:31,840 It tried to guess the right data type. 105 00:07:32,380 --> 00:07:36,870 So ABC represents text. In this case it's correct. 106 00:07:37,090 --> 00:07:44,150 The 123 here means it's a whole number which is also correct. Calendar box means it's a date 107 00:07:44,230 --> 00:07:45,610 and that's also fine. 108 00:07:45,610 --> 00:07:52,060 So once you get familiar with what these represent it's really easy just to cross-check if they are 109 00:07:52,060 --> 00:07:52,960 correct. 110 00:07:53,110 --> 00:08:01,060 Now especially for dates, make sure that power query recognizes these as real dates. Because if it doesn't 111 00:08:01,300 --> 00:08:06,530 you're going to run into problems when you analyse your data with pivot tables. 112 00:08:06,630 --> 00:08:13,570 So make sure they all have the correct data types. Now changing the data type here doesn't change your 113 00:08:13,570 --> 00:08:20,080 source data or actually any of the steps here doesn't change your source data. Your source data will 114 00:08:20,170 --> 00:08:25,610 always remain untouched but it changes the final information. 115 00:08:25,750 --> 00:08:29,080 Sales USD was seen as a decimal number. 116 00:08:29,140 --> 00:08:34,659 I'm just going to change it to currency. It asked me if I'm sure, I'm going to say replace it. 117 00:08:34,690 --> 00:08:37,929 And now we get the currency format up here. 118 00:08:37,929 --> 00:08:44,200 If for some reason your source data changes, it's not sitting in the same place or the name of your 119 00:08:44,200 --> 00:08:51,070 file changes you can go and edit that step by clicking on this gear icon right here. So just click 120 00:08:51,070 --> 00:08:55,430 on it and it brings up the features for that step. 121 00:08:55,510 --> 00:08:56,890 This was the file path. 122 00:08:56,890 --> 00:08:58,680 You can browse and change it. 123 00:08:58,720 --> 00:09:01,500 You can also change the delimiter and then click on ok. 124 00:09:01,520 --> 00:09:10,020 Any of these steps that has the gear icon beside it can be adjusted by just clicking on the icon. 125 00:09:10,030 --> 00:09:16,650 Now to improve our final result further I want to remove any rows that are empty. 126 00:09:16,690 --> 00:09:21,080 That's where we come to use the features that we see right here. 127 00:09:21,130 --> 00:09:27,190 A lot of the useful ones are in the Home tab but you'll find other great features in the transform and 128 00:09:27,190 --> 00:09:28,670 add column tabs. 129 00:09:28,690 --> 00:09:37,900 In this case to remove empty rows I just need to go to reduce rows, remove rows, and remove blank rows. 130 00:09:38,440 --> 00:09:42,140 So that checks if the entire row is blank and it removes it. 131 00:09:42,280 --> 00:09:43,760 And notice what it did here. 132 00:09:43,840 --> 00:09:46,070 It added another step. 133 00:09:46,090 --> 00:09:52,350 The other thing I wanted to do is to remove any rows from the final result 134 00:09:52,480 --> 00:09:54,270 if it doesn't have any cells. 135 00:09:54,310 --> 00:10:00,790 If this column is empty and I can filter this out. So I'm just going to click on the filter icon here 136 00:10:01,300 --> 00:10:03,460 and select remove empty. 137 00:10:03,460 --> 00:10:08,630 That basically filters out any rows that didn't have any sales. 138 00:10:09,220 --> 00:10:13,960 And I can see that step in here. If I add any steps by mistake 139 00:10:14,020 --> 00:10:17,770 I can just remove them by clicking on the X icon here. 140 00:10:17,770 --> 00:10:22,340 Let's say by mistake I remove this column right here. 141 00:10:22,390 --> 00:10:28,200 I'm just gonna select it, right mouse click, and click on remove. It adds a step here. 142 00:10:28,210 --> 00:10:32,470 If this was a mistake I'm going to click on the X and remove that step. 143 00:10:32,680 --> 00:10:35,620 I get my column back. Now here's the thing. 144 00:10:35,620 --> 00:10:42,250 Every time my query refreshes it connects to the data set and then it goes through all the steps and 145 00:10:42,250 --> 00:10:44,860 it gives me back the final result. 146 00:10:44,860 --> 00:10:47,650 That's the beauty of power query. 147 00:10:47,650 --> 00:10:53,830 So now that we've done the changes to our data set. Let's go and load it to our Excel sheet. 148 00:10:53,830 --> 00:11:01,330 I'm gonna go to close and load. This time close and load to. I get the option to load this into a table 149 00:11:01,420 --> 00:11:07,630 directly into a pivot table, pivot chart, or just create a connection to it without actually loading it 150 00:11:08,080 --> 00:11:14,770 into my Excel workbook. But I'm gonna load it in this case as a table. I'm going to select the existing 151 00:11:14,800 --> 00:11:23,100 worksheet and go with cell A3 and then click on OK. My data is loaded and it looks super clean. 152 00:11:23,170 --> 00:11:29,270 It doesn't have those empty rows and it doesn't have any sales values that were empty. 153 00:11:29,320 --> 00:11:30,820 Let's check one last thing. 154 00:11:30,940 --> 00:11:38,560 What happens when we change data or add data to our text file? Let's open the text file, scroll all the 155 00:11:38,560 --> 00:11:44,890 way to the bottom and add in some information. I'm just going to copy and paste this. 156 00:11:45,040 --> 00:11:53,680 Actually I'm going to add an empty row and I'm going to change this to new and new and 100 and 157 00:11:53,680 --> 00:11:54,660 200. 158 00:11:54,670 --> 00:12:00,350 Okay so we have an empty row and we have two new ones in there, Control+s and close. 159 00:12:00,580 --> 00:12:04,180 Now notice it says 136 rows are loaded. 160 00:12:04,180 --> 00:12:06,910 I haven't refreshed this dataset. 161 00:12:06,910 --> 00:12:08,410 Let's go and refresh it. 162 00:12:08,410 --> 00:12:10,770 Right mouse click, refresh. 163 00:12:10,990 --> 00:12:17,410 138 rows are loaded and I get the new information on the bottom and I didn't 164 00:12:17,410 --> 00:12:20,590 get any problem with that additional empty row. 165 00:12:20,590 --> 00:12:21,100 Why? 166 00:12:21,100 --> 00:12:27,930 Because power query went through all the steps I told it to do before it gave me the final result. 167 00:12:28,240 --> 00:12:31,360 So that's how you can import text files into Excel.