Power Query: Find a Table

Welcome to our Power Query blog. This week, I look at what to do when the location of a table is changed.

I have a query for some data that I have been receiving from my imaginary salespeople. However, when I try to open the query, I get an error message.

Power Query can’t find a table called Tent_Sales. The first step I should take is to look at the ‘Source’ step, which is usually the first step in my query.

The ‘Source’ step is also failing.  The M code in my source step is:

There is no mention of Tent_Sales here.  A good way to see where the error has come from, is to check the query dependency diagram.  This can be found on the View tab.

This gives me some clues. This query has come from a merge of Tent Sales and Tent Packs, and both queries have come from the current workbook. Therefore, I take a look at the Tent Sales query.

Apart from seeing the same error, I notice I have a ‘Go To Error’ button.

This takes me to the source step, which has the following M code:

I now know which step is pointing to Tent_Sales.  If that table no longer exists, I need to know what tables do exist in the workbook.  I go back to Excel and choose the Formulas tab.

I select the ‘Name Manager’, and I can see the tables that exist in the workbook. Tent_Sales7 looks suspiciously similar to the table that I am missing.

If I edit the name, I can see that the Table exists on the ‘New Location’ sheet, and the description tells me that this is the correct Table. Someone has copied the data from ‘Old Location’ to ‘New Location’ without realising that the Table name will change or that a query is dependent upon it. To fix this, I go to the sheet ‘New Location’.

If I access the ‘Table Design’ tab. I can see the Table name. I may either change it here or else change the query to use the new name. Since the original name is more logical, I opt to change the Table names on this sheet.

Now my Tables have their original names back, the queries are loading correctly. Having seen how easy it is to move and rename Tables, I decide to make sure that the sheet is protected.

I can just protect the sheet without a password, which will alert the user that the sheet is protected, but allow them to unprotect it, or otherwise I may create a password. This will allow me to stop users from changing the data on the sheet – however I MUST take note of the password used.

If the sheet is protected, not only is the user prevented from changing the data in the sheet, but they are also prevented from changing the table name in the ‘Name Manager’ (notice the greyed-out buttons).

My Tables are now “safer” and the queries load correctly.

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