Power Query: Three Sheets to the Table
9 June 2021
Welcome to our Power Query blog. This week, I look at combining sheets where the columns do not match.
I have some data that has come in from John, one of my imaginary salespeople. He has sent in some sales data, and I need to pull it into one table. The data is spread over three sheets, which are all in a similar format:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
In my (new) Excel workbook, I go to the Data tab and choose to ‘Get Data’ and then ‘From Workbook’, viz.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
This allows me to see the data in John’s workbook:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
Because the columns are not the same for each sheet, if I tried to append my sheets, I would end up with the dates in the middle of my data, which is not what I want. I need to transform each sheet before I combine them. However, there is another option I can take if I right-click on the workbook:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I can transform the data in the workbook. I select this option.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
This gives me the source step (I know I could have chosen sheets and deleted steps to get to this point, but this is neater!). I make sure that I am only including those sheets I want to combine.
Next, I create a duplicate query: I am going to work out what I will do with one of my sheets.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I call my new query Process_Sheet.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I filter to keep Sheet1, and select the Data column, which I right-click in order to remove the other columns. I can then expand it to see my data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I don’t need a prefix.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
If I am going to append my data, I need the dates to be in a column. First, I promote my headers, which I can do in the Transform tab by ‘Using First Row as Headers’. If a ‘Change Type’ step is created, I must delete it, so that the column names are not referenced (as they will be different for the other sheets).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I can then select the Tent Type column and unpivot everything else.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image12.png/a1537847463e660a31158c8032525438.jpg)
This looks good; I can now move onto the next step. This query is going to be a function, but first I need to set up a parameter, which will indicate which sheet is being processed. I can manage parameters on the Home tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image13.png/917da985be13220165c8d2823e95344f.jpg)
I create a new parameter, Sheet.
Sheet must be text, and it will be required by my new function. To begin with, Sheet will have a value of ‘Sheet1’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
Back in my Process_Sheet query, I go back to the ‘Filtered Rows’ step where I selected ‘Sheet1’. I am going to use the parameter instead.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I choose my new parameter and click ‘OK’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image16.png/d082e3477129350b8a2a589156028e63.jpg)
Because the default value is ‘Sheet1’, the parameter works in the same way as before. Now I need to make this query into a function.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
I am prompted for a function name, which I call fx_Process_Sheet.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
My function is ready to use.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image19.png/06909e3fbfc8faf87de7555c0b99e9d3.jpg)
Back in my original query, I want to invoke my function. In the ‘Add Column’ tab, I can ‘Invoke Custom Function’ and pass in the sheet name.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image20.png/173ef895b62ce391674d271b99c4d178.jpg)
Clicking ‘OK’ gives me a new column. This should hold all the data I need.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image24.png/c20c20624705a0da5ec0bb162b062eab.jpg)
I can now expand it.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image21.png/bc31e9359fd69b42bc104de457d8f9f8.jpg)
I select all columns and click ‘OK’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image22.png/059b7a0f2b398df09f943b811a5b3126.jpg)
I have my data, I just need to change the data types on the Transform tab, rearrange it, and rename my columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/236/image23.png/287595eacf557e9bf11ea5283a715ce1.jpg)
Come back next time for more ways to use Power Query!