Linking old names to new names over YoY analysis with a lookup

[sarah2_williams]

Hi all,

I have some yearly sales data where between one year sales and another the coding of the product has changed. I have attached an example of what I have been given, one tab is the sales data itself, the other is a lookup which relates the old product code to the new product code.

I need to somehow link the old codes to the new codes using the lookup, so that all years appear in a graph/table, with the products linked together. 

We know from the lookup that Old Product "1111" is the same as New Product "111" but I can't work out how to get this all together so that my graph will include all 3 years.

I have though that if this was excel I would do a Vlookup, giving previous years the new code so I could just use the "New Code" column but that doesn't seem possible in PowerBI? my data has nearly 9 million rows.

https://1drv.ms/x/s!AgqFHfAlcE6nhnkazp_0PpOiM3Qo?e=XEiPwm very basic example file here.

Thanks

Sarah

[v-yangliu-msft]

Hi  @sarah2_williams ,

Here are the steps you can follow：

1. Select Home – Append Queries - Append Queries as New in power query

2. Select Replace Values to convert the N/A of the two columns to Blank

3. Select the two columns to be merged, select Merge Columns in Add Column

4. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @sarah2_williams  use the lookupvalue function to achieve this.

Import the lookuptable in BI and on your original table use loolupvalue(new code,old code, old code(main table)), this would bring in the new codes in front of the old ones and you would be able to run the analysis.

Kindly mark this as solutiom as accepted.

[sarah2_williams]

This did not work for me. it will not let me add in the last reference on the main table. new code and old code in the lookup are whole numbers, as is the old code in the main table. do you know why it would not let me select the final column reference?

Thanks