Power Query: Heading Off – Part 3
28 February 2024
Welcome to our Power Query blog. Today, I continue looking at an issue that occurs when I load to a Table with no headers.
I plan to show a particular issue with Power Query and Tables without headers. However, first I need to create the scenario, and I will show a few methods and tips along the way. I have two Tables of data:
- contains my salespeople’s expenses (Expenses)
- determines the expenses that will be covered by each supplier (Supplier_Limit).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1707821923.png/1b1203b78390d36ecca4c40344f0982b.jpg)
In Part 1, I created two [2] queries, and grouped Expenses.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1707821956.png/d7ba066800f3c1d456bedc1bac11de86.jpg)
Last time, I merged Expenses with the Supplier_Limit query to see if any limits have been breached. To achieve this, I created a new conditional column Flag:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1707822003.png/d3ebfd11a84c0917719fce03394100ee.jpg)
I may now remove the Flag Column. From the Home tab, I choose to ‘Close & Load To…’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1707822017.png/8152b01fb319316673797ee46bd13524.jpg)
I need to be able to control which queries are loaded to the workbook. In the workbook, I choose ‘Only Create Connection’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1707822060.png/6341abba7fe37b910e0140dcf6f47685.jpg)
This will create all the queries as connections, and I can decide which to load:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1707822077.png/6b1886dbe5e9539311d594af65616071.jpg)
Since I only need to load Limit_Exceeded to the workbook, I can right-click on that query and find the ‘Load To…’ option.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1707822115.png/2b02c4c1f4e200b40544105897d019ed.jpg)
This brings up the same ‘Import Data’ dialog that I saw earlier:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1707822142.png/49cebfb41130ed1268c65813049fb238.jpg)
I change the settings and check ‘Table’ and ‘New worksheet’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1707822161.png/30413dc2d47cbbf43f0bb0fcfa2b3467.jpg)
I click OK, and a new worksheet ‘Limit_Exceeded’ is created.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1707822180.png/95d5ff6ea56d844f0b7fe309bfd18ac3.jpg)
This data is going to be transferred to another system, which means the headings must be removed. I click anywhere in the Table to reveal the ‘Table Design’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1707822207.png/549f9bd65aa4a4658e0eec9cf26f6f91.jpg)
In the ‘Table Style Options’, I uncheck the ‘Header Row’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1707822225.png/31de362b8aade6578bf2b86f7c8fe2c9.jpg)
So far so good. I change some values in the ExpensesTable, and refresh the query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1707822244.png/deb197065f91482f66470d0143109967.jpg)
Everything looks fine, but next time, I will see what happens when the requirements change and I run the query for a single salesperson.
Come back next time for more ways to use Power Query!