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).

In Part 1, I created two [2] queries, and grouped Expenses.

In Part 2, I merged Expenses with the Supplier_Limit query to create Limit_Exceeded, which tells me if any limits have been breached.

In Part 3,
I loaded Limit_Exceeded to a new
worksheet.

I removed the header row and changed some of the data in Expenses.

Last time, I ran the query for a single salesperson.

I extracted this cell and created a parameter P_Salesperson

I used this parameter to limit the data in Expenses:

When I refreshed Limit_Exceeded, I checked the results:

The results correctly showed one row of data and no headings. However, things change when I select a different Salesperson:

When I refresh Limit_Exceeded, it looks rather strange:

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:

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:

So far so good. Now, I change the selected salesperson back to ‘Mary’ and refresh:

Finally, I change it back to ‘Newbie’. I can immediately see a problem:

I get a ‘Download failed’ message on Limit_Exceeded. When I look at the query, the data looks strange:

That’s definitely not right: this query should be empty!
On the View tab, I tick the ‘Column distribution’ box.

It is empty and yet showing a row! If I ‘Refresh Preview,’ from the Home tab, it looks like this issue is fixed:

However, when I go back to the Excel worksheet and refresh, I still have a problem:

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!