Power Query: Heading Off – Part 2
21 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:
- contains my salespeople’s expenses (Expenses)
- determines the expenses that will be covered by each supplier (Supplier_Limit).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1707821088.png/79e57a3651b858fe1b8fe3d358fb00c9.jpg)
Last time, I created two [2] queries, and grouped Expenses.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1707821108.png/119bbb9260451554db7d42721c516865.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1707821132.png/11f9fb6dbd278e23a5edfcdc6682160f.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1707821154.png/8d911766b142ef09f84b2b3b11a169dd.jpg)
I take the default ‘Left Outer’ join and click ‘OK’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1707821173.png/de16b9e11dcc999d466261201284c95d.jpg)
I call my new query Limit_Exceeded and expand the data in the Supplier_Limit column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1707821190.png/366920f56364f406dc4ce746a63e5861.jpg)
I only need the Expense Limit from Supplier_Limit:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1707821206.png/88aff19eb45e255f76b73685c5ade45b.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1707821225.png/52dcd62779e92cb1c74d9fe5309bac9a.jpg)
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].
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1707821251.png/3a44aecfa23d31dd64b39aba50596582.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1707821272.png/bfb21a7dc81bfe092fc081666586bcb6.jpg)
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!