Forum Discussion
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:Item Code Date Sales Old/Historical Item Code 2 1/1/2023 $ 100 1 2 2/1/2023 $ 200 1 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
Hello v-dineshya, I attempted to do it your way, but the main table is on a SQL server. I merged the two tables and now the main table on the SQL server is taking a long time to update from the server. Do you have any suggestions to speed up the process? I reduced the amount of data I was pulling in, but it is still slow.
Thank you,
Fools_Gold
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