Power Query: Heading Off – Part 6

Welcome to our Power Query blog.  Today, I look at how to solve 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. 

In Part 4, 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 changed last time when I selected a different Salesperson:

When I refreshed Limit_Exceeded, it had moved the data from the previous selection into the header row.

When I deleted the top row from the Excel sheet, things got worse!

Since the problem is being caused by writing an empty query to a Table with no headers, I need to change what happens if there is no data returned by Limit_Exceeded.

I could add a row if my table is empty.  One way to do this, it to create a helper query that I can append to my existing query.  I don’t need to include all the columns; one will be enough.  I create a new blank query:

This is going to be a very simple query!

I have created a list of one blank row by using the M code:

I need to convert the list to a table so that I may append it.  I use the ‘To Table’ option on the ‘List Tools’ Transform tab:

I take the defaults and create the table:

I go back to Limit_Exceeded, and choose to ‘Append Queries’ from the Home tab.

I select NoRows, and click OK:

I delete Column1, and ‘Close & Load To…’ from the Home tab (as I do not want to load NoRows to the workbook).  Having set NoRows to ‘Connection Only’, I view the results:

This is looking much better!  Clearly, it’s not ideal to have a blank row, but it is better than showing the wrong data.  I’ll refine this approach next time so that I don’t add a blank row if I have data.

Come back next time for more ways to use Power Query!