Search
Close this search box.

, ,

Link Notes to a SQL Server Table in Excel Using Power Query

While working at Microsoft I had access to the SQL data from many of the applications used by the Azure Capacity Planning and Supply Chain teams. One of my functions was to track the changes and movement made to capacity (server racks) across time.

To accomplish this, I used Power Query in Excel to link a notes column to inventory line items in SQL Server using self-referencing tables. This allows the notes to stay in sync with each line item after refreshing, filtering, and sorting the data. Let’s take a look at how this is done.

For this example, I’ll be using the IEX_Daily_Stock_Prices table from my IEX_Cloud_Data database to simulate how I set this up. (To see how I receive by-the-minute daily stock prices into this database visit my blog post: Cloud Data Pull Using JSON HTTP Request in Power Automate).

IEX Daily Stock Price DB
IEX Daily Stock Price DB

There are a few points to mention before I demonstrate how to set this up:

  • Your SQL table needs a primary key or unique identifier field for this work.
  • You can add one or more columns to the table in Excel, I’m using 2 in this example.
  • This can work with not just SQL Server but with any data type, including another Excel table.

Click on the link below to see the YouTube video of this blog post:

First step is to connect Excel to SQL Server by going to Excel–>Get Data–>From Database–>From SQL Server Database.

Connect Excel to SQL Server
Connect Excel to SQL Server

Next are the remaining steps connecting Excel to SQL Server.

  1. Select your SQL Server, for this example I’m using a local server, but you can also connect to a cloud hosted server.
  2. Provide your credentials to the SQL Server.
  3. Select the table to link to from the database and click “Connect“.
  4. You should now see the SQL table in Excel.

Next step is to add columns to the Excel table. I’m adding two additional columns as an example named “Notes Date” and Notes”, but you can add as many columns as you’d like. Just type in a header name for the column next to the last column in the table to create the new column.

Add Table Columns
Add Table Columns

Then go to the ribbon, select Data–>From Table/Range. This will open up the Power Query Editor.

Select Get & Transform Data From Table-Range
Select Get & Transform Data From Table-Range

I like to rename the new table as the same as the original but with a “_Notes” at the end to easily identify the two going forward. You can do this under Query Settings Properties shown below underlined in red.

Rename New Connected Table
Rename New Connected Table

Then go to the ribbon and select Close & Load–>Close & Load To.

Select Close and Load To
Select Close and Load To

Next select “Only Create Connection” and click on “OK“. This will bring you back to the main table.

Select Only Create Connection
Select Only Create Connection

You should now see two query connections, with one stating “Connection Only”. Next you want to edit the main table, the non-Connection-Only table by right clicking on it in the Queries & Connection pane. This will bring you back to the Power Query Editor.

Edit Main Table
Edit Main Table

From within the ribbon, click on “Merge Queries“. This will open the Merge Queries window.

Select Merge Queries
Select Merge Queries

In the drop-down menu select the Notes table. This will add the columns in the preview window underneath it.

Select Notes Table
Select Notes Table

This is where the primary key or unique identifier is required. Select it in each table as shown below. You should see the selections match at the bottom of the window. Then click “OK“.

Select Primary Key Columns
Select Primary Key Columns

This brings you back to the Power Query Editor window, scroll all the way to the right where you will see a new column in your query. Next select the button in the upper right with the double arrows pointing in the opposite direction.

Select Change Type Button
Select Change Type Button

This opens a drop-down menu, unselect all of the columns except for the new column(s) you added. It will be the last column(s) on your list depending on how many columns you added. Then uncheck the “Use original column name as prefix“. Then select “OK“, you should then see the column expand into the number of columns you added. Lastly, go to the ribbon, under the Home tab, and select “Close & Load“. This will bring you back to the main table.

Select Added Columns Only
Select Added Columns Only

You will see next to your added column(s), the duplicate of each. Go ahead and delete each of the newly added duplicate columns.

Remove Newly Added Duplicate Columns
Remove Newly Added Duplicate Columns

That’s it, you are now ready to add notes or any other reference information for each line item in the table. You can refresh, sort, and filter the data without worry of having your table out of sync with your reference data.

Finished Query
Finished Query

Share Buttons

Twitter
LinkedIn
Facebook
Reddit
Email

Related Posts

Categories
Microsoft Power Platform
Microsoft 365