Power Query Problems

Fix Access Lookup Fields Importing IDs in Power Query

Nimra MalikNimra Malik Nov 14, 2026 868 views

Question details

The user needs Power Query to return item codes from Access lookup fields instead of the stored IDs.

Fix Access Lookup Fields Importing IDs in Power Query
Product
Microsoft Access and Power Query
Device & OS
not provided
Scenario
Import an Access table whose lookup fields display item codes such as C52041 and S528478.
Observed behavior
Power Query imports values such as 1, 2, and 3 rather than the codes displayed by the Access lookup.
Before you start

In Access, identify the lookup field, its stored values, and the related table that maps each ID to an item code. Check the field's Lookup properties in table Design View; changing the display control alone does not convert existing IDs into codes.

Solution 1Recommended

Merge the item table in Power Query to retrieve codes

Use this approach when the Access table stores IDs and you want to keep the existing database structure.

Power Query reads stored table values, not the labels displayed by an Access lookup control. Merge the related item table to add its code column.

1
Import both tables

In Excel, select Data > Get Data > From Database > From Microsoft Access Database. Choose the database, select the main table and the related item table in Navigator, and choose Transform Data.

2
Check the join columns

In Power Query Editor, select the main table's stored ID column and the related table's matching ID column. Ensure both columns have compatible data types using the data type icon in each column header.

3
Merge the queries

Select the main table query, then choose Home > Merge Queries. Select the related item table, select the matching ID column in each table, and use a Left Outer join to retain all main-table rows.

4
Expand the item code

Select the expand icon on the merged column, choose the item-code field, and select OK. Set the resulting code column to Text, then select Home > Close & Load.

Merge the item table in Power Query to retrieve codes
Check unmatched IDs: A null code after the merge can mean an ID has no match in the related table or the join columns have incompatible values or types.
Free Microsoft Office alternative

Work with imported item-code spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with compatible Microsoft Office files. Open an exported spreadsheet in WPS Spreadsheet to review item codes, while using Access and Power Query to resolve the underlying lookup issue.

Review and work with item-code columns in spreadsheetsCompatible with Microsoft Excel XLSX and XLS filesFamiliar spreadsheet interface for an easier transitionFree and lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query show an ID when Access displays an item code?

An Access lookup can display a code from a related table while storing an ID. Power Query reads the stored table value, not the displayed lookup label.

Will changing the table lookup to a text box convert IDs into codes?

No. In Access table Design View, changing the Lookup Display Control to Text Box reveals the stored values but does not replace them. You must map the IDs to codes separately.

Can I keep IDs in Access and still show codes in Power Query?

Yes. Import both the main table and the related item table, then use Home > Merge Queries in Power Query Editor to join their ID columns and expand the item-code column.

How can a form combo box store the item code instead of the ID?

In the combo box Property Sheet, set Control Source to the code field and configure Row Source and Bound Column so the bound column contains the code. Check existing values and relationships before changing the field's purpose.