I have merged two tabels ( sales and forecast ). For all the rows coming from sales query the cost price column has a value. The forecast rows does not have that.
In order to calculate future metrics/KPI I need to make a Power Query transformation that populates cost price on all forecast rows. I would like to do some kind of refence to the ProductName (exits both on the sales and forecast rows) and pull the cost price from the sales rows. The ProductName can have multiple entries in the table, but will be the same for alle the rows. So maybe a find first/max or something would be fine.
However, I am not sure have to make this calcuated column with some sort of lookup to ProductName?