Power Query: Heading Off – Part 2

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

Last time, I created two [2] queries, and grouped Expenses.

The data is now in a similar format to the Supplier_Limit query.

I will merge Expenses with the Supplier_Limit query to see if any limits have been breached.  I use ‘Merge Queries as New’ from the Home tab:

In the dialog, I choose the merge on Supplier and Expense Type, taking care that they are selected in the same order on both queries:

I take the default ‘Left Outer’ join and click ‘OK’.

I call my new query Limit_Exceeded and expand the data in the Supplier_Limit column:

I only need the Expense Limit from Supplier_Limit:

I want to find the rows where the limit has been exceeded.  One way to achieve this, is to create a ‘Conditional Column’ from the ‘Add Column’ tab:

I have created a new column Flag.  The first clause checks for null in Expense Limit.  The  second clause ensures Flag will be one [1] if the Expense Limit is exceeded by the Amount.  This deals with the null in Expense Limit without needing an extra step to replace null with zero [0]. 

Now, I can filter on Flag.  I don’t need to change the data type of Flag as I will be removing it before I load the data:

Next time, I will load this to an Excel Table and show what happens when the Table headings are removed.

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