Power Query: Heading Off – Part 1

Welcome to our Power Query blog.  Today, I begin to look 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).  

I extract Supplier_Limit, by clicking somewhere in the Table, and right-clicking to ‘Get Data from Table/Range’. 

This gives me a query called Supplier_Limit:

I could load this query as a connection only and go back and get the other data in the same way, but there is another method I could use without leaving the Power Query editor.  I make a duplicate copy of Supplier_Limit:

I delete the ‘Changed Type’ step because it uses the column names, and look at the M code in the Source step:

I may use the current M code:

and change the Table name from Supplier_Limit to Expenses:

This gives me the contents of the Expenses Table, and automatically recreates the ‘Changed Type’ step:

I rename this query Expenses.

Before I can compare this date to the limits, I need to group by Supplier and Expense Type.  I can do this using the ‘Group By’ functionality on the Home tab:

Using Advanced mode, I have chosen Supplier, and then used ‘Add grouping’ to also group by Expense Type.  I have chosen to aggregate Amount, by using the Sum Operation.  

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

Next time, I will merge Expenses with the Supplier_Limit query to see if any limits have been breached.

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