PowerTable: Reference Columns need dropdown support for new record creation (Insert Row and Forms)
Product: Fabric IQ Plan (preview); PowerTable sheets
Context: Built a complete EPM proof-of-concept for budget planning global offices: Fabric SQL Database (star schema with fact + dimension tables) > Direct Lake semantic model > Plan item with PowerTable and Planning sheets. Grid editing of existing records with Reference Columns works perfectly, write-back, audit trail, and approval hooks all validated.
The gap: When creating new records, Reference Columns do not support dropdown/lookup selection:
- Insert Row in the grid: clicking a Reference Column cell returns "This column is restricted for editing"
- Forms: Reference Column fields render as plain number inputs ("Enter a number") with no dropdown or search capability
Users cannot select valid dimension values (e.g. pick "US" from a country list). They would need to know internal integer foreign keys, which is not viable for production.
Expected behaviour: Reference Columns should render as searchable dropdown selectors when inserting new rows, both in grid view and in Forms. The user picks a display value; the system resolves to the correct FK integer on save.
Impact: This blocks the primary use case Plan is designed for — governed budget/forecast data entry by non-technical business users. Without dropdown support on new records, organisations cannot replace Excel-based planning workbooks with Plan.
Workaround: None within Plan. External tools (Power Apps) can write to the same SQL table with proper dropdowns, but this defeats the purpose of a unified planning platform.
@
2 Comments
- I believe this scenario is already supported today through Lookup Columns. If you have a RegionCode stored in your database and a corresponding display label column (for example, USA) in the same table or a related lookup table, you can configure the ID column to display the label while persisting the underlying key value during save operations. To do this: Go to Manage Columns and edit the target ID column (e.g., RegionCode). Set Input Type to Single Select. Set Values Type to Lookup. Select the appropriate Lookup Schema and Lookup Table. Configure: Lookup Key Column = RegionCode Lookup Display Column = Region With this configuration, users will see and select friendly labels such as USA, while the corresponding region code is stored in the database when the record is saved. If the requirement is simply to expose the underlying key alongside the label for transparency when you are inserting a new Row and assigning a Region for it, that can also be achieved today by adding a Formula Column: Insert a new Formula Column (e.g., simply name Region Code (Read Only Purpose)). Set the formula to: [RegionCode] from the formula reference dropdown list Leave Create Database Column unchecked so that the column is used purely for display purposes. This gives users a read-only column showing the actual stored key while still allowing them to work with user-friendly labels in the lookup dropdown column. This should address the need to display both the friendly label and the underlying code without requiring users to enter or memorize key values.
Thank you for sharing your feedback and the detailed planning scenario.
For example, let's assume we have a star schema with an Employee table, where Department ID is a foreign key connecting the Employee and Department tables. This scenario can be achieved by configuring the Department ID (Foreign Key) column as a Single Select column with a Lookup Table, rather than as a Reference column. How it works:
Edit Existing Row
1. When a user edits an existing record and updates the Department, the Department ID column displays a searchable dropdown populated from the configured lookup table.
2. The user selects the required Department Name, and on save, the corresponding Department ID (foreign key integer) is stored automatically.
Insert Row
1. During new record creation, the same searchable dropdown is available on the Department ID column.
2. The user simply selects the required Department Name from the list instead of entering an internal foreign key value.
3. On save, the selected Department Name is resolved to its corresponding Department ID and stored in the underlying table.
Forms
1. The same lookup experience is available in Forms as well.
