• Save a copy of the workbook and work in the copy.
• Use Excel for Windows (Microsoft 365, or Excel 2016 or later).
• Have your database login ready, and check the old workbook refreshes on your computer today.
• If the file name ends in .xlsm, it contains macros. Ask IT to check them at the end.
• Do one table at a time.
• All Power Query users must be created as SQL users and assigned the appropriate SQL Roles by Tes Support.
• Do one table at a time.

1. Go to Data > Queries & Connections and click the Connections tab in the panel on the right.
2. Right-click the connection and choose Properties. The Used In tab shows the sheet and cells it fills.
3. On the Definition tab, note the name after DSN= in the connection string. On the Usage tab, note which refresh options are ticked. Click Cancel.
4. Click inside the table on the sheet and note its name from Table Design > Table Name.
   
   

1. Right-click inside the old table and choose Table > Edit Query. The MS Query window opens.
2. Note down what you see: the database table(s) at the top, the columns shown, any filters in the Criteria area (turn it on with View > Criteria if it's hidden), and how the rows are sorted.
3. Close it with File > Cancel and Return to Microsoft Excel, so nothing changes.

1. Go to Data > Get Data > From Database > From SQL Server Database.
2. Pick the DSN name you noted in Step 1 and click OK.
3. Sign in to the database if asked.
4. In the Navigator window, open the list and tick the same database table you noted in Step 2.
5. Click Transform Data. The Power Query Editor opens.

1. Columns: click Home > Choose Columns and tick only the columns the old query used.
2. Filters: click the arrow on a column heading and set the same filter as the old query's criteria.
3. Sort: use the same arrow to sort the same way.
4. Headings and order: double-click a heading to rename it so it matches the old table exactly. Drag columns into the same order.
5. Types: click the icon on each heading. Set dates to Date, and IDs that start with zero to Text.

1. Remove the old connection. In Queries & Connections > Connections, right-click the MS Query connection and choose Delete. The old table keeps its data but no longer refreshes.
2. Move the table name across (only if Step 3 found formulas using it). In Table Design > Table Name, rename the old table to OLD_Table, then give the new table the original name. Go to Home > Find & Select > Replace, click Options, set Within to Workbook and Look in to Formulas, and replace OLD_Table with the original name.
3. Clear the old table. Select all of it, including headings, and choose Home > Clear > Clear All. Don't delete rows or columns, as that can break formulas.
4. Move the new table in. Select the whole new table, right-click and choose Cut (not Copy). Right-click the old table's top-left cell and choose Paste.
5. Right-click the table and choose Refresh to confirm it works. Delete the empty extra sheet.
   
   

1. In Queries & Connections > Queries, right-click the query, choose Properties, and tick the same refresh options you noted in Step 1.
2. Point any PivotTables at the table name (PivotTable Analyze > Change Data Source) and refresh them.
3. Save, close, reopen and click Data > Refresh to confirm everything works.

Step 3:

Create a SQL Server Database connection using Data > Get Data > From Database > From SQL Server Database.

Review the results and Close & Load the query.

• About Power Query in Excel
• Use Microsoft Query to retrieve external data
• Create, edit, and manage connections to external data
• Import data from data sources (Power Query)
• Manage queries (Power Query)
• Rename an Excel table
• Refresh an external data connection in Excel
• Set privacy levels (Power Query)