Fix Access Lookup Fields Importing IDs in Power Query
Question details
The user needs Power Query to return item codes from Access lookup fields instead of the stored IDs.

- 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.
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.
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.
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.
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.
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.
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.

Store item codes directly and use a form combo box
Use this approach if the table should store the item code itself rather than a separate ID.
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.

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.




