Self-Referencing Query, Explained

I have heard people talk about self-referencing queries for a few years now.  Up until recently, I have never had the need to try them.  But then I saw a video on ExcelOffTheGrid.Com that covered them in great detail.  What Mark showed really got my attention; made me think, can I use this in production?  I had to find out.

I know several Excel MVPs that use these in production, so I decided to give it a try, build a few of these, learn the ins-n-outs, and to do a blog post on what I learned.

* In this post, David shows his take on this, using a self-referencing query in a real client solution ( “Making an Excel Database” ).

 

A self-referencing query is a query that uses itself as the source.   Instead of pointing at an Excel Table, the query points at itself.  This is not standard.

 

Would a Self-Referencing Query be Useful in a Real-World Solution

For this demo we wanted to see if a self-referencing query could be useful in a real-world scenario, for a paying client.  I built a simple workbook demonstrating the concept.  The demo has just enough detail to allow me to apply several cool techniques, while keeping it simple enough to easily comprehend.  Please DM me if you would like a copy of the workbook.

What I learned:  I can use a self-referencing query when I want to append external records to a local Excel Table, via Power Query, without using macros, that is significant!!!

Else, it functions much like an Excel Table, with some interesting differences that need to be noted.

Possibly Confusing:  In that it looks like a Query, but if you delete the data and hit Refresh, the data does not refresh, the data is gone, you just deleted it.

Also Concerning:  Typically, you want to keep the users out of the Tables, but in this case, you actually want them to interact with the Table.

What it Does:  It allows you to add new records to a Table, no need for macros, based on a Join in Power Query.

Would I Use It:  Personally, I would use it in a macro-free workbook; else I would not use it.

Why People Use It:  It also allows you to manually enter data into the Power Query Table, and when you Refresh the Query, the data stays.  That is the point, you can add data into the Query, and it will stay, properly associated with the correct row.   

Same with calculations, they will stay as well.       Some call this a Hybrid-Table.

 

In this post I Show Two Examples of a Self-Referencing Query

1. I first show a complex solution, using a SRQ as a Power Query REA Form ( Table ), using External Data.
   1. High level summary, the concept.
2. Later in the post I show step-by-step, how to create a very simple self-referencing query.
   1. The steps with images shown will show you how to create self-referencing query.
3. Neither example uses VBA.

 

 

 

 

---

 

A Self-Referencing Query Real-World Solution – TheNewExcel.Com

Guest Co-Post:  David – Excel MVP, TheNewExcel.Com

The following guest section was originally written and published by David of TheNewExcel.Com. It is republished here with permission.

Making an Excel Database

 

Below is David’s take on the self-referencing query.  In David’s solution he did leverage the power of VBA.

You can see the full-post on David’s site:  https://thenewexcel.com/making-an-excel-database/

• I know, Excel is not a database………, he said it, I did not.

 

 

 

Making an Excel Database

Power Query is one of the most powerful tools inside Excel today. You’ve probably used Power Query to import sales, customer, or payroll data into Excel. If you need to import data into Excel, there is no better tool than Power Query.

But . . . with a small adjustment, Power Query can do more than just import records. It can execute all four standard database actions: 1) Create, 2) Read, 3) Update, and 4) Delete. You can create a database right inside Excel.

How? Easy!

Enter the self-referencing query.

Let’s have a look.

 

Self-Referencing Queries

All power queries have a Source . . . the place the data comes from. Well, what happens if the Source for a Table is the Table itself? That . . . is a self-referencing query . . . or SRQ.

 

 

 

Normally, if you edit a worksheet cell that is part of a power query, that change disappears when you refresh it. But . . .if you change a value in a self-referencing query, that value stays when you refresh it!

You’re probably saying, “Big deal!”

So am I.

A self-referencing query doesn’t really make sense on its own. However . . . when you add a few steps to an SRQ, it becomes a full database action tool.

Let’s see it in action.

Excel “Database” . . . and Its User Interface

Here is our self-referencing query: a “database” of customer info.

 

And here is an input page connected to that SRQ. This input page allows us to Create, Read, Update, and Delete records in the master database table.

 

We can select an existing customer – reading the master database. Or, we can change a field and “save this record”. That record is now updated in the master database.

While a macro automates the action, this isn’t VBA-driven. The VBA code simply refreshes the query. THAT is the power of a self-referencing query (SRQ).

 

Advanced Database Functions with an SRQ

That’s read and update. Now, let’s create a “new customer”, enter some data, and “save this record.”

 

 

When our SRQ is refreshed, the new record is added to the database.

 

 

 

Finally, the SRQ allows us to delete records. With our user interface, we select a record and press “Delete This Record.”

Just as with update and create, this isn’t VBA-driven. The macro is just refreshing the SRQ.

The input sheet has a hidden cell called rng_Delete. If this cell is set to TRUE, the Power Query steps in the SRQ will remove this record.

 

This is powerful, so let’s recap.

An SRQ, paired with an input sheet, allows us to create, read, update, and delete records in the master SRQ table.

Almost no VBA.

Just a self-referencing query.

With established Power Query steps and already-written, easy-to-adjust VBA code, we can quickly add database functionality to any spreadsheet using SRQs.

 

Guest Co-Post:  David – Excel MVP, TheNewExcel.Com

End of guest contribution by David of TheNewExcel.Com.

 

 

 

 

---

 

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

Contact us for a Free Consultation Today.

We are here to assist with custom solutions in Microsoft Excel 365 with Power Query.

Try our one-on-one Mentoring Services

 

 

---

 

Self-Referencing Query with an REA Form

In a recent post we covered our use of Power Query Tables as REA Forms.  There we used a small amount of VBA.

The use of a Power Query Table as an REA Form is something we do regularly, something we pioneered.

As in this demo, we often use external data, brought in via Power Query, Table loaded to Sheet.  We then use that PQ Table as a Form.  This method allows the user the opportunity to edit any record before it is added to the data Table.  See the full post to learn more.

• The PQ REA Form ( Table ) is a way to slow down incoming data, and to allow the user time to revise the data, before adding it to the data Table.  It can be simple or complex.

 

Power Query REA Forms are our focus when it comes to Excel Trackers w/ Dashboards.  Trackers are one of our most common solutions.

 

 

Macro-Free Workbook Method – Enter the SRQ

But what do you do if you want to do the same thing, but in a macro-free workbook?  How do you take records from an external Table, edit them, and then append them to a local Excel Table without the use of code?  Simple, you use a self-referencing Query tied to a Power Query REA Form.

 

 

 

---

 

Demo 1:  Using a Power Query Table as an REA Form with a Self-Referencing Query.

Below are the basic steps the data travels through in the process.  There are three queries used in this demo.

1. External Excel Data into Power Query.
2. Power Query Table loaded to a Sheet.
   1. Used as the REA Form.
      1. Loaded into Power Query.
         1. Join to SRQ
3. Add New Records to the SRQ via the REA Form.
4. Add New Records in the Self-Referencing Query, directly.
5. RefreshAll dos the trick, no VBA needed.

• Add Validation Lists, CheckBoxes, Conditional Formatting, Data Validation, etc.

 

 

The Video-Demo #1

In the video below we show how the self-referencing query works.  Simple example, several techniques, no vba.

 

 

 

The External Data File

Below is an image of the external data file, an Excel Table.

Note, several Regions are purposely misspelled, to be corrected in the REA Form.  One record is set as Excluded.

 

 

The REA Form

The green Table below is out of Power Query.  Here we use it as a Form.  We allow the user to Review, Edit, and Append ( REA) any and all records in the Query, before they are loaded to the local Data Table.

• RefreshAll will move the approved records. VBA Is not used.

 

We do use:

1. Data Validatio Lists.
2. CheckBoxes.
3. Conditional Formatting.
4. Active Record Lookup
5. CPK, 3-attributes.

 

 

The Self-Referencing Query

The Green Table below is a Query loaded as a Table to a Sheet.   But this Query is a little different.

It looks like a Query, but it works more like a Blue Excel Table; delete the data in the Query, and when you Refresh, it does not return.

If you add a record to the bottom of the Query, it stays on Refresh.

If you change any cell, in the Table, when you Refresh, it stays, associated with that row.

If you properly place calculations in the Query Table, on Refresh, they stay.

But if you delete a record and hit RefreshAll, it does not return, much like an Excel Table.

 

We do use:

1. Data Validation Lists.
2. CheckBoxes.
3. Conditional Formatting.
4. Filters in the Query:
   1. Change Record Type to Delete, on Refresh, the record is deleted.
5. CPK, 3-attributes.

• RefreshAll will update the records in the SRQ.   VBA Is not used.

Both Queries Together:

Fun little demo, you can watch the records move, see the changes, follow the logic.

The Design:

Here I try to layout how the file works, visually.

 

 

The Queries

Just three queries do the trick.

 

 

How to test it:

1. Add new records to the external data file.
2. Refresh the REA Form.
3. Make one or more changes to the data.
4. RefreshAll.
5. The new records are now in the SRQ Table.
6. Make one or more changes to the SRQ Table.
7. RefreshAll
8. Look at the results.

• How did it work?

 

~That is Demo #1 ~

 

---

 

~This is Demo #2 ~

 

 

What is a Self-Referencing Query?

A self-referencing query is a query that uses itself as the source.   Instead of pointing at an Excel Table, the query points at itself.  This is not standard.

This is what some call a Hybrid-Table; partially an Excel Table, partially a Power Query Table, in how they act.

 

In a Self-Referencing Query, if you delete the data and Refresh, the data is gone;  If you change the data, it stays changed.

Refresh does not bring it back to how it was.

 

A Self-Referencing Query Allows:

1. Append records from one Table to a Query Table without the use of vba. ( Like Copy & Paste Values )
   1. In macro-disabled solutions, this is useful, very useful.
2. It allows you to add data to a record and have that data stay associated with that record. This is also very useful, and this is why most people use a Self-Referencing Query
   1. Add a Notes column, add a DropDown, add a CheckBox.
      1. The selections stay on Refresh.
      2. Normally, if you add a formula to a Query, on Refresh the value stays, and the formula vanishes.
         1. Use Mark’s method and the calculation stays live in the Query.

 

 

In this simple demo #2:

The Primary Key is the Record ID & Record Type.

The “Exclude” Filters records in Power Query and allows the user to exclude records from the pending merge.

Several of the columns are in the query and not in the Excel Table.  Power Query populates a few of those, the user populates the others, onsheet.  Hence, a self-referencing Query.

It functions more like an Excel Table than a Query.

 

The Query in the image below joins the two tables in the second image.

 

 

“New Records” in the blue table are added to the green table on refresh.  Macros are not used.

 

 

 

 

 

---

 

Steps to Create Self-Referencing Power Query

There are a series of steps needed to create a sef-referencing query.  We will go over each in detail here.

See images below for better understanding.

1. Create a new Excel Table
   1. Name it tblDataSource.
   2. Design it the final table layout, with all of the desired columns, etc.
   3. Populate with dummy data.
   4. This Excel Table will be used to create the Self-Referencing Query in the next step.
      1. Note: The Excel Table is used as source data.
         1. This may be changed to an external data file once the basic design is complete.
            1. For now it simplifies the demo.
2. Load the Excel Table, tblDataSource, into Power Query
   1. Rename the Query SRQ.
      1. For the moment, make no changes to the query nor the data types.
   2. Close & Load the Query as a Table to a new tab in the workbook.
   3. Open Power Query, change the Source of Query to itself, SRQ.
   4. Close and Load the Query.
   5. This is now a Self-Referencing Query.
      1. But at this point, it does not do much, if anything other than it flattens calcs and such.
   6. Test the file.
3. Load the Excel Table, tblDataSource, into Power Query, again.
   1. Name it tblDataSource.
   2. Close & Load as “Connection Only”.
   3. Note: The Excel Table, tblDataSource, is used as the source data for the demo.
      1. This may be changed to an external data file once the basic design is complete.
         1. For now, it simplifies the demo.
   4. Note: The tblDataSource Query will show the records in the tblDataSource Table but not in the SRQ Table.
      1. This Query will be used in a Join, to find new records.
         1. This query will not be loaded to sheet, no need to see it.
4. Merge the SRQ Query with the tblDataSource Query
   1. Right Anti-Join, only rows in second (tblDataSource )
      1. We want ‘New’ records only.
   2. Select the new column in the Query (tblData), then “Remove Other Columns”.
   3. Expand the tblData column in Power Query.
   4. Append both data-sets.
      1. This is the hardest part; you use M-Code.  But it is not hard at all, once you know what to do.
      2. Here I used: “= ExpandedtblData & Source” to combine two steps.
         1. Not intuitive, I know. Hard to explain here, see Mark’s video.
5. Test the File:
   1. Delete all the records from the Green Power Query Table.
   2. RefreshAll.
      1. Notice the changes to the Green Table.
      2. All of the records in the Blue Excel should now appear in the Green Power Query Table.
   3. Make changes to one or more records in the Green Table.
      1. RefreshAll, notice any changes, should be none.
   4. Add one new record to the Blue Excel Table.
   5. Refresh the Green Power Query Table.
      1. Notice changes.
   6. Delete all records from the Blue Excel Table.
   7. Refresh the Green Power Query Table.
   8. Notice changes.
   9. Delete one or more records from the Green Table.
   10. RefreshAll.
   11. Notice the changes.

 

 

Super simple demo.  In this video, the Blue Table is a local Excel Table, not an external Table.

 

 

 

That is the Basic Setup for a Self-Referencing Query

But we can take that up a notch. We can change the data source to be an external data source, using Power Query.  We can add a bunch of Defaults, Calculations, Lookups, you name it.

 

If we really want to take it to the next level, add some vba.

 

 

---

 

 

Step 1 – Create an Excel Data Table

For this simple demo, we want to use Power Query to pull-in available items from an external data source.  We are doing this demo to show how easy it is for Power Query to isolate new records.  New records would be records in the external data file, but not in the local Excel Table.

1. Create a new Excel Table in Excel 365.
   1. Populate the Table with mock data.
   2. This will hold the available data.
2. Save as an Excel workbook in the Data folder.
3. Close the file.

• Each time the user updates the external data file with new records, those records will be instantly available in our workbook.

 

 

 

Step 2 – Load that Excel Table into Power Query

1. In Master File:
   1. Using Power Query,  import the data from the external Excel workbook.
   2. The workbook has one Excel Table on Sheet1.
2. Save the file.

 

 

 

Step 3 – Transform the Data

1. Rename the Query
2. Transform the data as needed

 

 

Step 4 – Close & Load the Power Query Table to a New Worksheet

1. Close & Load to a Table on a Sheet.
2. Change the Properties.

 

 

 

Step 5 – Change the Source of the Query to Itself

Yes, this feels weird, but you must change the Source of the Query to be itself.

 

• Refreshing at this point simply replaces calculations with values.
• Delete the data, hit Refresh, and nothing happens.
• Change the data, hit Refresh, nothing happens.
• It is acting more like an Excel Table than a Power Query Table.
  • Be careful here; do not permanently delete your data.
• Close & Load.
• Test the Query.

 

 

 

Step 6 – Add Column(s) Inside the Query

In the Query Editor, Add Column.

1. Add your new column(s) inside the Power Query Editor, not OnSheet.
2. Close and Load the Query.
3. The Query Refreshes
4. You get an error message.
5. You open the Query, and you remove the newly added column(s)
6. The Error message goes away.
7. Close and Load Query.
   1. Issue resolved.

 

 

 

 

 

 

 

 

 

Step 7 – Add Onsheet Calculations / Calculated Column

Adding a new column with a function to the Query, and wanting the function to stay, when the query refreshes might be a new concept to you.

It is common to add a new column with a function, to the far-right side of the Table.  But this is a self-referencing query, and that complicates things.

 

Now here is where it gets tricky.  That is due to the self-reference, and continuous column creation issue.  This solves that.

 

Here we add a new column directly onsheet, say to the end of the Power Query Table, right side.  Let’s create a Primary Key calculation, and let’s set this up so that when the query refreshes, the function remains.

1. Add new column to the onsheet PQ Table.
2. Add the TEXTJOIN Function to the column, creating a Primary Key field.
3. Open the Query Editor,
   1. Select all of the columns except the new column, and select Remove Other Columns
4. On Refresh, the calculations remain.

 

 

 

 

 

 

 

Step 8 – Adding “New” Records from an External Data Source

A very common need.  Show me a list of items that are in our data source but not in our historical data table.

New records would be records that are in the data source, but not in our self-referencing Power Query Table.

To do this we need to create a Join in Power Query, so that we can isolate new records.

1. Upload the Source Table into Power Query.
2. Load to “Only Create Connection”
3. In Power Query, Merge the two queries.
   1. Right Anti Join
      1. Show new records.
4. In PQ, Select the new column, right-click, Remove Other Columns
5. Expand Table, showing all columns
6. Merge two Steps in this Query.
   1. Yes, weird, but it works
7. Close and Load Query.

 

 

Step 9 – Adding the Bells & Whistles

Now you can stop there, it works; it adds new records to the Table, without VBA, and the manual columns and calculations remain, on Refresh.  Perfect.

What can we add for the user. How simple can we make it.

1. DropDown Lists
2. CheckBoxes
3. File Path Selector
4. Conditional Formatting
5. Data Validation
6. Cell Protection.
7. VBA / Macros

 

 

 

 

 

---

 

Conclusion: Self-Referencing Query Method

~ To Use or Not to Use ~

 

In Conclusion:   After building the three demos, speaking with a few MVPs on that topic, and after writing this post I have determined that unless I wanted to work in an .xlsx format versus an .xlsm format, I would prefer to use the Excel Table with the VBA based Copy & Paste Special method over the Self-Referencing Query method.

Self-Referencing Queries:  Very cool in that it can append records without the use of VBA.  That is useful, that is powerful.

 

 

 

~ Our Signature use of Power Query Tables as Data Entry Forms ~

See what we do with this concept in 2027.  So many businesses are looking for such a solution.  Here at Excel and Access, LLC we build them daily.

 

 

---

 

 

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

Contact us for a Free Consultation Today

 

 

---