Correcting Data

Hi Fools_Gold,

I believe you could go that way.
However, I would recommend an alternative approach. Why not update the main Item Code itself with the new item code values and store the old Item Codes in a separate column (Historical Item Code instead of New Item Code)? Basically you would have an structure like this:

Or you could create a mapping table with the same concept in mind.

This way you would not have to change anything downstream (for example, with this approach you won't need to change the mapping to a new column in the Pivot table) and you will maintain the history as well. And this is actually a standard pattern we follow when preserving the history over time. However, if there is any particular aspect of your data that prevents you from adopting this pattern, please go ahead with your current implementation plan and let us know if you hit any roadblocks.

Cheers!

Hi Fools_Gold ,

You want to replicate the Excel VLOOKUP approach in Power BI using Power Query merge, and it works logically but slows down refresh performance since the fact table lives on SQL Server and the merge happens on Power BI desktop, forcing it to pull all data before joining.

Please try below options.

1. Instead of merging in Power Query, perform the join directly on SQL Server so Power BI pulls pre-merged data.

Create a SQL View

CREATE VIEW vw_Fact_WithNewItemCode AS
SELECT
f.*,
COALESCE(m.NewItemCode, f.ItemCode) AS NewItemCode
FROM dbo.FactTable f
LEFT JOIN dbo.ItemMapping m
ON f.ItemCode = m.OldItemCode;

Then connect Power BI to this view instead of the raw table.

2. Load both tables In Model view, create a relationship Fact[ItemCode] --> Mapping[OldItemCode] . And create a calculated column in DAX.

NewItemCode =
COALESCE(
RELATED(Mapping[NewItemCode]),
Fact[ItemCode]
)

Note: This replicates your Excel VLOOKUP but inside the model, not during data refresh.

For testing the SQL code, I took sample data .Please refer below output SQL snap.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh