Excel Magic Trick 1357: Power Query Function to Repeat Clean & Transform Steps for Many Excel Files

welcome to Excel magic trick number 1357 if you want to download this Excel workbook excel magic 1357 start or the finished file so you can follow along click on a link below the video hey we got a great video here we're going to learn how to build a power query function to repeat clean and transform steps for multiple Excel files now we'll go look at the files in just a moment but here's going to be our full list of all of our files and inside each one of those files is a cross tabulated table now look at this table we have products here but then at the top of each column we have the fare name and the date and then the inside our unit sold not only that but the sales rep name is going to be in the sheet tab so we need to take all of these files and we will actually build a power query function that will clean and transform each one of these files and give us one proper data set now let's go look at our Excel files we're going to go over to Windows Explorer when you download the files there'll be a zipped folder called one three five seven source files you'll have to unzip it now inside that folder is a start and for excel files if I double-click a look inside the start these are the four files we're going to start with now if I double click and open one of those you can see there is our cross tab table with two column headers product over at the front of each row and there is our sales rep name I'm going to close this file now this video is going to have a lot of step it's going to be a power-packed power query lesson with all sorts of amazing steps now let's go over to the sheet create function what I did is I took one of the files and pasted it here that way we can perform the steps to unpin looting both criteria or conditions at the top of each column unpin it create one proper data set and then we'll copy that code and use it to create a function that can be used on all of the other excel files now in order to get this into power query we're going to have to convert it to an Excel table so with the single cell somewhere in this table I go up to insert table or I use the keyboard ctrl T I'm definitely unchecking my table has headers click OK I'm going to need both of these items down inside the table to do my transformation now I want to name this table so I go up to design and I'm going to name it I call it something long I called it start data to build power query function all right so I have a single cell in this proper Excel table I'm using Excel 2016 so my power query is up in data get and transform if you're in Excel 2010 or 13 you actually have to download it it's a separate power query tab now if we're going to take our data from an Excel table and bring it into power query we have to use the from table button or we can use the keyboard alt apt now here's our power query editor window we have our name and there are some steps we do not need the change type so I'm going to point in the applied steps pane to that red X and click to delete it now here's our cross tabulated table to conditions at the top and one condition at the head of the row we need to unpick this but one of the steps is going to be to promote headers and since we have two conditions or criteria here at the top of each column and one at the head of the row we actually want to promote just the row header for product so we first need to transpose this table get the date and fair name into a single column and the product name at the top of each column so I've come up to transform and there's the transpose table button there we have our date and fair and the product at the top of each column now we need to promote the headers from the first row so we come to the icon in the upper left hand corner use first row as headers or I can simply use this button up in the transform group and there it is the product is now the column header now we need to unpick these so that products are in a single column now think about this what this column means is each one of these units is for the Belen product so when I unpin it it will create with these two conditions and that unit balint these two conditions that unit and balance so from this column right here we'll get five records in the resulting unfitted proper data set now I need to select date and product fair so I hold shift click on product fair to on pivot I go to transform drop-down and unpin it other columns or right click on pivot other columns and look at that we have one two three and four columns now we need to rename some of these and I'm not going to change the data type because we'll actually change the data type in our next query date is a fine name I'm going to double click product and fear and just call it fair and enter double click attribute we're going to call this product and enter double click value and we'll call this units and enter now there are our steps we're going to save this query by going up to home close and load close and lo - I definitely want to load it as a connection only and click load there it is and we loaded it as a connection only query instead of creating in our first step here the actual function because it's easier to come back and edit and debug this than it is a power query function but I'm going to need that code and so watch this I'm going to right click that query and point to do okay I immediately want to come over and name this query I'm going to call it FX - I called it unfit each fair table and enter now in order to convert this to a function we have to go up and look at the M code View ribbon tab advanced editor now let's take a look at our M code it's always going to start with let each line will have the name of this step an equal sign and then the function that's enacting the code for that line followed by a comma but notice this first step doesn't have double quotes and a pound sign yes there's no spaces so we don't need those but why in the world does this step have double quotes and a pound sign because to differentiate a step name from actual text like down here for date attribute value we have a pound sign at the beginning that differentiates it from text and says this is the step name now there's the equal sign the function and for each one of these lines and each one of these functions you always have to list the previous step which is the output from that step is going to be further transformed in this line of code notice pound sign double quote transpose table that step is listed right there and then if you look in each function there's the previous step name previous step name we finally end with in and you have to repeat the name of the last step because that's going to be the output for power query now for us as a function we do not need this first line so I'm going to highlight it and hit delete there's our four lines of code to convert it to a function I click before let enter enter up arrow up arrow and we're going to list the variable input because remember for us we're going to be getting a bunch of files that have a cross tab table so our variable is going to be this first step table dot transpose and be fair table now I'm going to double click copy and come up here and define this as a function open parenthesis ctrl V there's our variable name close parenthesis and then an equal sign and a greater than symbol that defines the function it now knows to take that variable name and look down here it's as if that arrow is pointing down here and it's going to use it down here in the EM code that is our function when I click done there is our function notice over here it has a single applied step and that's much different than our first query where we had many steps now we're going to load this home and I'm just going to click close and lo because it will only load as a connection that means we have access to that function in other queries now our goal is to get all of those files in so now our third query will be data new query from file and from folder we click browse I loaded my start folder to the desktop but notice we're telling power query please get all the files from inside that folder click OK click ok in this intermediate step let's click edit now it lists each one of the files and attributes for the file we definitely want to come over here and the very first step is to name it this is going to be our consolidated table I called a consolidated fare data table and enter now you can see attributes like extension well because we're in power core we could actually use this filter to prevent any other file types besides dot xlsx now if we were sure that we only had dot xlsx in that folder we wouldn't have to do this step but I'm going to be super careful here the first thing I'm going to do since sometimes extensions can be all-capital I'm going to right click transform and say please only show as lower case there's our step added over here now I can come to the drop down text filters and I want to save only import things that are equal to the extension dot xlsx and click OK now we don't need any of these but let's just take a look at the content column it says binary but when you click on it notice it's given this icon here and in an excel file there may be lots of objects like sheets tables define names now that little button right there if these were text files not Excel files we can simply click this to expand the columns now I just read an article this downward-pointing double arrow in power bi desktop now can handle excel files hopefully that feature will come to excel power queries soon but for now we can't click that to get out the data we're actually going to have to remove all the other columns right click remove other columns and then add an extra column to get at the excel objects from in the excel file so we go up to add column custom column I'm going to call this get excel objects and the function we use is Excel dot workbook now we have to be careful with power query functions spelling and case matter so I'm very carefully have Excel workbook and there's the content field that's the name of this field over here that has each Excel file double-click close parenthesis and click OK now if I click on off to the side next to table I can see well it's got a different set of columns for each file the name of the object the type of the object and so on so now we're going to use the expand button be sure to uncheck use original column names as prefix and click OK now watch this in the data column if I click next to table there's our preview that's the cross tab table notice it comes in his column one column two column three and when we created our code for our function we started off our table that way so now we can add an extra column and use that M code to convert each one of these files that's pretty amazing before we do that let's make sure that each excel file has only the objects we want now kind lists the type of objects now we only want sheets as opposed to tables and define names so I'm going to click the filter button text files and this column should only have items that are equal to sheet and notice I better spell it right and have that s capitalized click OK now that's the object type but also someone might have had a sheet in one of these that accidentally had the default name sheet 1 sheet 2 sheet 3 so I'm going to filter this column 2 I'm being extra careful here text filters and I want sheets to be important that do not contain the word sheet click OK now the only two columns we're going to need our name and data so I have name hold control click data right click remove other columns now we can add our extra column with our function and look at this I have the latest version of power query and instead of using custom column which is easy enough they have an invoke custom function button the new column name is going to be something like unfitted fare tables and there it is a function query drop-down now we only have one function you could have many but there it is unfit each fare table and we want that function to work on the data column so I'm going to come here drop down and make sure it says data click OK and now click next to table there is the unpiloted table for each one of our excel files that's pretty amazing now we need to get rid of data so wry click remove we can now use the expand button to get at each one of these tables there are the field names click OK and there we have the sales rep plus each one of our unfitted tables stacked one on top of the other now let's clean this up I'm going to change the data type instead of going up to transform data type drop-down I'm going to use this icon click and this is going to be text click this is going to be date icon this one's going to be data type text icon data type text and the data type for this one will be hold numbers so we changed each one of the types we can see that step down here I'm going to make sure that each one of the names are what I want I don't want name I want sales rep and enter date Fair product units looking good now we have used our function we have our end results and now we go to home close and load close and load two and we have three choices now we can dump it as a table into the Excel spreadsheet I could also do only create a connection and actually access through the pivot table dialog box if I had a small data set sometimes that's convenient I also could check add this to the data model especially if I have big data that is the way to go I'm going to unchecked all I'm dumping it as a table on a new worksheet click OK now I can see the query over here a hundred and eighty-two records have been loaded and there is the data set sales rep date fare product and units pretty amazing to take all of those crosstab tables one from each excel file and create one proper data set I'm going to come down here and double click something like fair data table and enter now we want to test our query because the reason we went through all of these steps is we have this query pointing towards a folder so let's go over to windows explore and I'm going to select file mo hold shift all the way to Tyrone ctrl C double click start ctrl V now there's the folder that power query is pointing to there's 8 file so now when I come back over to excel right click refresh and look at that now there's 389 rows loaded from 8 crosstab tables one in each excel file that is pretty amazing now in this video we saw how to go from a bunch of Excel files each as a crosstab table build a power query function use it in a query and create one updatable proper data set all right we'll see you next video

Channel: ExcelIsFun

Views: 78,292

Rating: 4.948791 out of 5

Keywords: Excel, Microsoft Excel, Highline College, Mike Girvin, excelisfun, Michael Girvin, Mike excelisfun Girvin, Excel Magic Trick, Power Query, Power Query (Get & Transform), Import Multiple Excel Files, Power Query Function, Power Query Parameter Query, Unpivot Power Query, Clean Cross Tabulated Tables, Create Proper Data Set, Transpose, M Code, Power Query M Code Function, Import Files From Folder, Invoke Custom Power Query Function, Import Excel Files

Id: _csX8sCzJd0

Length: 18min 42sec (1122 seconds)

Published: Sat Dec 10 2016