Power Query: Heading Off – Part 5

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:

1. contains my salespeople’s expenses (Expenses)
2. 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!