Power Query: Heading Off – Part 4

Welcome to our Power Query blog.  Today, I continue to consider 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.  

Last time, I  loaded Limit_Exceeded to a new worksheet.

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

This time, I will see what happens when the requirements change, and I run the query for a single salesperson.  On the ‘Expenses’ sheet, cell N2 contains the salesperson value I want to use.  This cell may be identified by the name P_Salesperson:

If I right-click on cell N2, I have the option to ‘Get Data from Table/Range’.  

This creates a new query called P_Salesperson:

Note that Power Query has decided to promote headers, leaving an empty table!   The ‘Changed Type’ step is also ineffective since there is no data to work with.  I only need the Source step, so I delete the other steps:

I click in the cell and right-click to ‘Drill down’:

This gives me the parameter I need. 

Since I will be reducing the data, I should apply the parameter as close to the source as possible (ignoring the option of doing this in Excel as this is a Power Query blog!).  In the Expenses query, I go to the Source step:

I may add a filter on Salesperson:

I choose ‘Mary’ as my placeholder value.  I will change this to P_Salesperson later. When I click OK, I received a warning:

I may ignore the warning and click Insert, as I am happy to insert a step.

The M code for the ‘Filtered Rows’ step is:

To use the parameter, I change this to:

This gives me the same results:

My final query now looks like this:

Now I have reduced the data in Expenses, I check the Limit_Exceeded query.

I now only have one [1] row.  I ‘Close & Load To…’ as I did last week, so that my parameter query can be set to ‘Connection Only’:

I refresh Limit_Exceeded and look at the results:

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

More on this next time!

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