How to Combine Multiple Files with Inconsistent Column Headers into a Single Sheet

let's say you want to combine multiple Excel workbooks but each one has a different column header for example when file has rip the next one has sales rep and the third one has account manager here's how to combine these in just four steps let's open up a blank query go to data get data from other sources and click on blank query here in the query editor let's bring in our source data right click on the pane on the left here go to new query file and click on folder I've copied the path for my sales data folder so let's paste it and click on open here we can see our region 1 and Region 2 files in our sales data folder let's click on combine and transform data [Music] [Applause] here in the combine files dialog box how query chooses our first file as the sample file so when we connect to a folder to combine all our files power query creates a query called sample file which is used as an example to process each file it's important to note that the end result of our end query will depend on the columns that exist in the sample file you can click on the drop down to select any of your files as the sample but let's keep our first file which was region 1 as our sample file let's click on sheet1 to select it remember this was not in a table format but that's fine as power query will perform the Transformations for us to convert this to a table and click on OK and here are all the Transformations that power query has performed for us to get our end result here which is a table called sales data and it has our region 1 and and Region 2 sheets combined if we take a closer look though our region 1 data has imported correctly but we have these null values here for region 2. this is because we used our region 1 file as our sample file so power query based our query on the column headers that exist in this file and as our Region 2 file did not have these column headers it shows up as null values in our end query but we're going to fix this in the steps to come next let's rename a column so that we have the code for how power query performs the step but first we need to ensure that we perform this transformation in the correct query we have our end result which is our sales data query and we also have our transform sample file query which is the example that power query used to process the rest of our files remember our aim is to create a query that dynamically combines our data that has different column names so we need to First create our query to dynamically update our column names so that when the tables are combined all the column names are exactly the same in each table therefore we will need to perform the rename column step here in the transform sample file query and not in the end query which is sales data so let's rename rep to sales rep and hit enter and over here in our applied steps power query has recorded the rename column step let's take a closer look at the code the power query generated for this step here in the formula bar it used the table.rename columns function this function renames our current column name with the new column name the first argument is referencing the promoted header step which is our last step before we performed the renamed column step this is the sequence in which M code works if you would like an introduction into this code and how the advanced editor works I highly recommend you watch this video here the link is in the description also the next two arguments in the curly brackets show the current column name which is rep and the new column name which is sales rep the curly brackets indicate that this argument needs to be in a list so important to note the table that rename columns function requires our current and new column names to be in a list we can also see that rep and sales rep have been hard coded as it's in red and in double quotation marks but we want this argument to be dynamic and we need it to be in a list so let's create a list that we can use to replace the section of hard coding which will enable our query to be dynamic on the left here in our queries pane right click go to new query file and click on Excel workbook and let's double click on this workbook called column names I've created this table called column names which has our current column name and the new column name and this is what we will use to make our query Dynamic and let's click on OK and we get a query with our column names table and power query has performed some Transformations for us we need to convert this table to a list as remember the renamed column step requires the renaming argument to be a list so in the transform tab click on transpose so we now have our current name in one column and the new name also in the same column fact this is still a table as we can see from the icon on the left here to convert this to a list we need to use the advanced editor in the Home tab click on the advanced editor and here is the code that power query uses to generate our query it shows the formula for each step so for example for our transposed table step this is the formula that it uses so it uses the table.transpose function on our last step to return a result which is the change type step so it references this step now to convert our table to a list let's insert a comma after the close brackets here when you're writing M they always need to be a comma after each step in the lead section except for the last step let's press enter so we go to the next line and let's call this new Step column names list equals and type table dot to columns and the intellisense brings it up this function will convert our tables to a list and insert the Open brackets and start typing transposed and it brings up our transpose table step so we can select that from the intellisense why are we using the transpose table step well we want our table.2 columns function to be performed on our table and the last step to return our table is the transpose table step so we need to reference that applied step in this function and remember there is no comma after the last step in the late section then over here in the in section is where our output is generated from the let section it currently shows the transpose table as our outputs so let's change this to column names list as we want that to be our output and let's hit done and we now have a list of nested lists so what our formula has done is create a list for each of the four columns that we had so our first column has rib as our current name and sales rep as our new name and so forth and this is in a list before we move on to the next step could you please do me a favor once this video is finished could you head over to the community tab of my channel I've posted a question there to find out how you use power query and the type of Industry that you're using it in for example use it to clean and analyze data and you're in the fitness industry thank you so much now let's get back to our video now we have our column names as a list as we can see from this icon on the left here we're going to insert this list into our formula for renaming columns so in the transform sample file table we need to insert our list here in the renamed column step and we can do that here in the formula bar we have our table that renames column function power query performs this function on our last step which is the promoted header step and in the curly brackets we have our current name and the new name and these names have been hard coded so let's delete everything after the comma and start typing column names then let's insert a comma and start typing missing field and missing field dot ignore comes up so let's select that this parameter indicates the power query that if there is a missing field in our table then ignore that and insert a close bracket and let's hit enter and we've now created a formula to dynamically change our column names but now if you look on the left here we've had an error in our sales data query it shows the column rip of the table wasn't found it's no problem because this is really easy to fix the reason for the error is due to rep being hard-coded in one of our applied steps and if we click on the change type step the error shows up here and if we click on the expanded table column step our query has no errors so it's just a change type step resulting in this error and if we look in our formula bar we can see that rip has been hard-coded so let's remove the change type step and our error is fixed if you encounter more errors like this I highly recommend you watch this video here which shows you how to fix errors caused by the change type step and reorder column step the link is in the description also next let's select our source name column and in the transform tab click on the drop down next to extract and let's select text before delimiter and our delimiter is the dot as we want the text before the dots to be returned and click on OK let's rename this column to region and let's use Ctrl a to select all our data and in the transform tab click on detect data type and let's change our sales value to currency and let's send this back to excel well and we have our region 1 and Region 2 Data all combined into one table now a new Region 3 has been opened let's have a look at this file the First Column header is account manager then we have sales number status and sales value let's have a look at our column names table we don't have account manager here so let's add it here in our current column and we want this to be renamed to sales rep so let's save this and go back to our query and let's hit refresh and our query correctly updates with our Region 3 sales data now if you have a set of data in this format where you have the field name on the left and the detail of that field on the right and what you actually want is this format here where you have each of your field names as the column headers and the details as the values in each of those columns then you should definitely check out this video here it's going to save you a lot of time in your data cleanups

Channel: Miss Microsoft

Views: 15,220

Keywords: power query advanced, excel in power query

Id: SKskTCNiUIc

Length: 12min 34sec (754 seconds)

Published: Sat Dec 10 2022