Correcting Data

[Fools_Gold]

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

[alish_b · Accepted solution]

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!

[v-dineshya]

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

[Fools_Gold]

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

[v-dineshya · Accepted solution]

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

[v-dineshya]

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

[v-dineshya]

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

[Fools_Gold]

Hello @v-dineshya,

I did not attempt your second solution, because all I really needed was to connect a SharePoint Excel and add the new items below and the old items in the same column and match them to the new items in another column. Then when the user chooses a new item in a slicer in Power BI, both the old and new come up. This solved my confusion without having to merge with a SQL server.

Thank you,

Fools_Gold

[v-dineshya]

Hi @Fools_Gold ,

Thank you for the update. Is your issue resolved? If not, please provide more details about the issue and let us know the expected outcome or result.

Regards,

Dinesh

[Fools_Gold]

Hello @v-dineshya,

My issue is resolved. Thank you!

Thank you,

Fools_Gold

[v-dineshya]

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

[alish_b · Accepted solution]

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!

[Fools_Gold]

Thank you @alish_b!

[Ashish_Mathur]

Hi,

That should work.  Try it.

[Fools_Gold]

Thank you @Ashish_Mathur!