Forum Discussion
Correcting Data
Hello Power BI Community,
I am importing data from a source where my item codes have been constant for many years. Now, I want to switch item codes, but keep the history for the item codes that are related to the historical item codes. I was wondering how in Power BI, I could make this relationship happen. Where it would show the new item code, but with the historical data. As you can see below, the item code "1" was historically the item code used. Now, in 2024, I switched the item code to "2", but it is the same thing. As you can see, the first pivot shows the data as it is showing. The second pivot shows it with the New Item Code VLOOKUP. This is what I am trying to do in Power BI. I debated creating a Sharepoint where it has Old Item Codes and New Item Codes and then creating a relationship with that table and my import table, but I do not know if that would work.
Thank you,
Fools_Gold
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
13 Replies
Hi,
That should work. Try it.
Thank you Ashish_Mathur!
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!Thank you alish_b!
Hi Fools_Gold ,
Thank you for reaching out to the Microsoft Community Forum.
You want to replicate your VLOOKUP function in Power BI
Please follow below steps.
1. Created sample tables Fact table and Mapping table with sample data. please refer snap.
2. Created a mapping table that relates old item codes to new item codes.
3. In Power Query --> load the both tables and select fact table --> Go to Home --> Merge Queries --> Match the 'Fact'[ItemCode] with 'Mapping'[OldItemCode] and choose left join and click ok. And expand the merge column to show the 'NewItemCode' field.
4. In table visual, Drag the 'NewItemCode' and 'Sales' from fact table.
Please refer below output snap and attached PBIX file.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi Fools_Gold ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @Fools_Gold ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
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
