Hello, on some of our forms we have Group/Service/Function custom lookups for users to select. The values of these lookups are reported onto a powerBI dashboard. We have just recently restructured the organisation so those lookup values have changed, we want to change the values of the lookups but keep the historical data in powerBI as the old names.
Does anyone have a good suggestion on how best to achieve this?
Hi. I found the following information. I hope this helps. Thanks! Jane
Handling organizational restructures without breaking historical Power BI reporting is a classic data management challenge.
When lookup values change, the main goal is to prevent users from selecting outdated values on new forms while ensuring historical records in Power BI retain their original values (or allow side-by-side historical vs. current-state analysis).
Never edit/rename existing lookup values in place: Renaming an existing lookup option will retroactively alter all historical records linked to that ID/value in reports.
Never delete legacy lookup values: Deleting them causes missing or null values in historical Power BI reports.
Add new options & deactivate old ones:
Add the new Group/Service/Function values to your form lookup lists.
Mark the legacy values as Inactive / Disabled / Archived (if supported by your form/lookup tool). This hides them from dropdowns on new forms while leaving the records intact in the underlying database.
If your system uses key/code-based lookups (e.g., GRP_101), keep the old keys associated with old names, and assign new keys (e.g., GRP_201) to the new names.
Instead of relying solely on raw text from the source system, create a Dimension Table in Power BI (e.g., Dim_Organization or Dim_ServiceLookup) to govern mapping.
Your dimension table should include the following structure:
| Source Lookup Value | Display Name (Historical) | Unified / New Mapping | Status | Effective Start Date |
|---|---|---|---|---|
|
| Legacy Group A | Corporate Service Team | Deprecated | 2020-01-01 |
|
| Legacy Service B | Shared Operations | Deprecated | 2020-01-01 |
|
| New Group X | Corporate Service Team | Active | 2026-09-01 |
|
| New Service Y | Customer Experience | Active | 2026-09-01 |
Depending on how your stakeholders want to analyze data, build two distinct fields into your Power BI semantic model:
Historical View (Point-in-Time):
Uses the raw or historical display name stored on the ticket/form at creation time.
Use Case: "How many tickets were logged by Legacy Group A back in 2024?"
Current / Unified View (Mapped State):
Uses the mapped new structure name via your mapping dimension table.
Use Case: "What is the 3-year overall trend if we map all past and present tickets to our new organizational departments?"
In Power Query, merge your fact table (form submissions) with the mapping dimension table:
Use a Left Outer Join on Source Lookup Value.
Add a conditional column or COALESCE logic: if a record predates the restructure or has a legacy value, keep the historical name; if it's a new submission, display the new value.
Ensure unmapped or custom text entries fall back gracefully (e.g., "Unmapped - [Original Value]").
| Do's 🟢 | Don'ts 🔴 |
|---|---|
| Add new lookup options alongside existing ones. | Do not rename existing lookup option text in place. |
| Set legacy lookup options to Inactive/Hidden. | Do not hard-delete legacy lookup options from the database. |
| Use an Org Dimension Table in Power BI for mapping. | Do not overwrite historical database rows with new org values. |
| Define clear point-in-time vs. current-state fields in Power BI. | Do not hardcode translation logic directly inside DAX measures.
|
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
