Power Query: Heading Off – Part 5
13 March 2024
Welcome to our Power Query blog. Today, I explore 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-1707930685.png/1537c3bf56af7cfee5937320e8e58753.jpg)
In Part 1, I created two [2] queries, and grouped Expenses.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1707930723.png/5ba696bf0365f01e4e9bc81e011fc7fd.jpg)
In Part 2, I merged Expenses with the Supplier_Limit query to create Limit_Exceeded, which tells me if any limits have been breached.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1707930739.png/e2a8f4be3ee27ce8fc73002fe378c044.jpg)
In Part 3,
I loaded Limit_Exceeded to a new
worksheet.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1707930785.png/5cd0bc0b0aeae6e5ea20c764788a8bab.jpg)
I removed the header row and changed some of the data in Expenses.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1707930806.png/2b20ae7108554ede97394f2d9bceffd8.jpg)
Last time, I ran the query for a single salesperson.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1707930838.png/16006d196af677df714f29cb0283051f.jpg)
I extracted this cell and created a parameter P_Salesperson
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1707930855.png/f8962274bbe0fd03987614ab6a5da1b4.jpg)
I used this parameter to limit the data in Expenses:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1707930874.png/0c748127fa0d0eabaf78150100900986.jpg)
When I refreshed Limit_Exceeded, I checked the results:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1707930891.png/fd5b2e2327022efd4dc5da8607b6c6fe.jpg)
The results correctly showed one row of data and no headings. However, things change when I select a different Salesperson:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1707930915.png/4a9c00129c3f630b891ab3f75cad3f94.jpg)
When I refresh Limit_Exceeded, it looks rather strange:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1707930938.png/5effc325be64109c11bfaf4a6adad418.jpg)
It shows a row of data where the header row would have been. Note that the Limit_Exceeded query indicates that zero [0] rows have been loaded. If I look back at the data for Mary, I see that, instead of showing an empty table, Mary’s data has been pushed into the header row:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1707930963.png/b198c5d3cf809f8949eaa49e4f813a90.jpg)
Whilst this could be described as a bug, I still need to fix it so that the output data is correct. I could assume that if I move the Table up a row, this problem might not happen. Let’s delete the top row:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1707930984.png/a528937b16a7ffc7b0dd14bf334d4241.jpg)
So far so good. Now, I change the selected salesperson back to ‘Mary’ and refresh:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1707931140.png/1007550d64fca4e343c301d734044ab5.jpg)
Finally, I change it back to ‘Newbie’. I can immediately see a problem:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1707931168.png/725c5330c9ed597dcb43daa6180013c4.jpg)
I get a ‘Download failed’ message on Limit_Exceeded. When I look at the query, the data looks strange:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1707931004.png/16664147ba0cd3995cb79c7da6fe6082.jpg)
That’s definitely not right: this query should be empty!
On the View tab, I tick the ‘Column distribution’ box.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1707931018.png/4088b92a3e9403669745bc315fd555c6.jpg)
It is empty and yet showing a row! If I ‘Refresh Preview,’ from the Home tab, it looks like this issue is fixed:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image18-1707933240.png/146bd8ee8746ed7023e937f3c39635c4.jpg)
However, when I go back to the Excel worksheet and refresh, I still have a problem:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image19-1707933258.png/c786229d1fb0ff3f7b694b42dc70bf7a.jpg)
Clearly, I need to do more to solve this problem, which is where I will pick this up next time.
Come back next time for more ways to use Power Query!