Power Query: Re-bind a Query to a Worksheet / Table that has been Deleted and Re-Created

Power Query is a powerful tool in Microsoft Excel that allows users to import, transform, and analyze data from various sources. One common issue that users may encounter is when a query is bound to a worksheet or table that has been deleted and re-created. This can cause the query to break and display an error. In this article, we will explore how to re-bind a query to a worksheet or table that has been deleted and re-created.

Before we begin, it is important to understand the concept of queries in Power Query. A query is a set of instructions that tells Power Query how to import and transform data. When you create a query, it is bound to a specific worksheet or table in your Excel workbook. If that worksheet or table is deleted and re-created, the query loses its binding and needs to be re-bound.

To re-bind a query to a worksheet or table that has been deleted and re-created, follow these steps:

Step 1: Open the Power Query Editor

First, open the Power Query Editor by clicking on the "Data" tab in the Excel ribbon and selecting "Get Data" and then "Launch Power Query Editor". This will open the Power Query Editor window.

Step 2: Locate the Query

In the Power Query Editor window, locate the query that is bound to the deleted and re-created worksheet or table. The queries are listed on the left-hand side of the window under the "Queries" pane. Click on the query to select it.

Step 3: Edit the Query

Once the query is selected, click on the "Edit" button in the toolbar or right-click on the query and select "Edit". This will open the Query Editor window where you can make changes to the query.

Step 4: Re-Bind the Query

In the Query Editor window, click on the "Home" tab in the ribbon and then click on the "Advanced Editor" button. This will open the Advanced Editor window where you can edit the query's M code.

In the Advanced Editor window, locate the line of code that specifies the source of the data. This line of code will look something like this:

In this example, "Table1" is the name of the deleted and re-created table. To re-bind the query to the new table, replace "Table1" with the name of the new table. If you are not sure of the new table's name, you can find it by going back to the Excel workbook and checking the name of the re-created table.

After making the necessary changes, click on the "Done" button in the Advanced Editor window to close it.

Step 5: Apply and Close the Query

Back in the Query Editor window, click on the "Close & Apply" button in the ribbon to apply the changes and close the Query Editor. This will re-bind the query to the newly created table.

That's it! You have successfully re-bound a query to a worksheet or table that has been deleted and re-created. The query will now import and transform data from the new table.

It is important to note that re-binding a query to a new table may cause some changes in the data structure or column names. You may need to make additional adjustments to the query to ensure that it continues to import and transform the data correctly.

By following these steps, you can easily re-bind a query to a worksheet or table that has been deleted and re-created in Power Query. This will allow you to continue working with your data and perform the necessary transformations and analysis.

References