Forum Discussion

Iron Contributor

Jul 14, 2021

Solved

#REF error after Power Query refresh

I create a table using a Power Query. Next to this table I added columns with formulas referencing the table (eg. [@ProductRef] or tCalc[@ProductRef]). This works fine until I refresh the Power Query...

excel

power bi

Yea_So
Jul 15, 2021
Lorenzo & bartvana

A power query + data model + power pivot solution can also be considered by creating relationships from supplier table and product table as dim tables (lookup tables) to the parameter/calc table (fact table) then, display the results into a power pivot especially if supplier/product calculation is high volume would be most efficient.

Lorenzo

Silver Contributor

Jul 15, 2021

Hi bartvana

Based on the sample you uploaded I would advise you to do a merge in Power Query between tables tSupplierProduct and tProductRef, instead of performing an XLOOKUP. That would reduce the risk of issues like the one you've been experiencing

Hope this makes sense

Yea_So

Bronze Contributor

Jul 15, 2021

Lorenzo & bartvana

A power query + data model + power pivot solution can also be considered by creating relationships from supplier table and product table as dim tables (lookup tables) to the parameter/calc table (fact table) then, display the results into a power pivot especially if supplier/product calculation is high volume would be most efficient.