0

In an on-sheet Table object, how to do a lookup against external data in one column, while allowing data entry in other columns?

To say it another way: How to have editable columns in a query-table, such that the editable columns are referenced by the query?

I'm not trying to pivot. It's just a flat table.

Excel version: 2016

Scenario

I have a Stores table on an Excel sheet. The Manager column is blank: (sorry if tables display wrong, they look fine in SO preview)

Stores (worksheet table): | Store_Number | City | Manager | ------------ | -------------- | -------------- | | 1 | Rye | | | 2 | Rye | | | 3 | Derby | | | 4 | Napa | |

There's an external data-source containing the Managers table:

Managers (external datasource): | City | Manager | many non-relevant fields... | | ------------ | ------- | - | | Rye | Joe | | | Derby | Sue | | | Napa | Lizzy | |

In the on-sheet Stores table, how can one column lookup Manager from the external table, while allowing users to insert or update rows in other columns? The Manager name should automatically update.

Constraint

The solution mustn't require loading the complete external table onto a worksheet. Assume it's 10 million records.

Connection methods

I think there are a few ways to connect to it from Excel:

  • Get external data button
  • New query button
  • Connections
  • PowerQuery ("PQ")
  • PowerPivot ("PP")

Potential solutions

I think there are some possible solutions:

  • create a relationship between the two tables in the data model
  • create a cell formula which does a lookup against the external data
  • write a VBA UDF which executes SQL statement against the external data
  • create a measure
  • PQ merge

UDF: It seems this requirement shouldn't require VBA, but i'm not against it.

VLOOKUP: I can't figure out if a worksheet formula, like VLOOKUP, can use an external datasource as the lookup table.

MEASURE: I think measures are intended for pivot tables. Not trying to pivot.

RELATIONSHIP: i like relationship or join, because it's based on a table-to-table relationship, rather than cell-formulas, and the PQ/PP very intuitive and drag-drop.

PQ MERGE: seems like the most appropriate method.

These threads may offer clues, i'm not sure:

Attempted PowerQuery method

This almost works, but still requires loading the external data into a separate sheet-tables. The benefit is, it retrieved only those external rows which match my source sheet-table. Still requires a VLOOKUP in my entry table.

Add sheet-table connection:

  • Click any cell inside the sheet-table.
  • Click Data ribbon > Get & Transform > From Table
  • Click Close & Load To... > Only Create Connection > Load
  • The Workbook Queries pane will open, showing the sheet-table.

Add external data connection:

  • Click Data ribbon > Get & Transform > New Query > desired external data source (in my case, SharePoint list, but source-type shouldn't matter).
  • In Navigator window, select desired external table, and click Load To...
  • Click Only Create Connection > Load
  • In the Workbook Queries pane, right-click the external table > Edit. Click Choose Columns, and unselect all but the lookup and return columns. This will help speed up refreshes.
  • The external lookups will display "Table". Click the expand button. enter image description here
  • Click Close & Load

Merge:

  • The Workbook Queries pane will open, showing external table and sheet-table.
  • Right-click the sheet-table in the Queries pane > Merge.
  • In the Merge window, select the external table in the bottom drop-down list. Then highlight the matching column in each table. Click OK.
  • The PQ editor will open, showing the join output.
  • Again, the external lookups will display "Table". Click the expand button. Unselect the match-column, since the sheet-table already provides that.
  • Click Close & Load To.
  • Click Existing Worksheet and select a cell next to the on-sheet table. Click Load.

Result: We now have two tables side-by-side on the sheet: the original sheet-table, and lookup-results table. That's great.

Unfortunately, i want them to be the same table, so i can edit manual data in one column, and get the matching lookup from the external table in a different column in the same sheet-table. Maybe this can be achieved with the help of some VBA.

For now, the best i can do is put some VLOOKUPS in the entry-table, pointing to the lookup results in the other table.

I tried inserting a new column into the on-sheet merge-table, but that gets removed when you refresh.

johny why
  • 2,047
  • 7
  • 27
  • 52
  • 1
    For self-referencing tables in powerquery see https://exceleratorbi.com.au/self-referencing-tables-power-query/ – horseyride Oct 25 '21 at 19:44
  • Thx for this. I'll have to contemplate how it might help. In your article, there's no external data, which i need. I think it might be done by reversing his data-flow. Start with a table linked to the external DB. Now create a new table, which is a join between itself and the first table. Now add more columns to the new table. So, it still requires two tables, but at least it's based on the join defined in PQ. – johny why Oct 26 '21 at 01:51
  • if vba is an option you could trick the user by adding a simple vba that copies the powerquery result table to a hidden sheet. The user can update the values in the result table > vba will copy the updates to hidden sheet which is the source for powerquery. – ceci Oct 26 '21 at 12:32
  • @ceci Thx, but i don't quite follow. "vba that copies the powerquery result table to a hidden sheet" -- You mean the query result? i think PQ can load the result-table to a sheet without any VBA. "user can update the values in the result table" -- unclear. The query result-table can't be edited. "hidden sheet which is the source for powerquery." -- Ok, so the hidden table is the *source*, not result? thx – johny why Oct 26 '21 at 22:01

0 Answers0