Please note javascript is required for full website functionality.


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:

  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.

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:     

I may now remove the Flag Column.  From the Home tab, I choose to ‘Close & Load To…’:

I need to be able to control which queries are loaded to the workbook.  In the workbook, I choose ‘Only Create Connection’:

This will create all the queries as connections, and I can decide which to load:

Since I only need to load Limit_Exceeded to the workbook, I can right-click on that query and find the ‘Load To…’ option. 

This brings up the same ‘Import Data’ dialog that I saw earlier:

I change the settings and check ‘Table’ and ‘New worksheet’:

I click OK, and a new worksheet ‘Limit_Exceeded’ is created.

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:

In the ‘Table Style Options’, I uncheck the ‘Header Row’:

So far so good.  I change some values in the ExpensesTable, and refresh the query:

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!