#REF error after Power Query refresh

[bartvana]

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, then I get #REF errors.

I have use the same method in other worksheets, and it works fine there. What could be going wrong in this one?

I already tried changing the External Data Properties, didn't help.

[#2]

@bartvana 

cannot be done that way, please provide a sample file since power query is a complicated function to explain.

cheers

[#3]

Thank you for looking into this!

Attached a simplified sample workbook. Strangely enough, in the sample workbook everything works, while in my real file, which does basically the same, I get the #REF error.

Still, it would be great if you could look into the sample workbook and advise on best practice.

[#4]

@bartvana 

So var is the price * Qty

Question: I am assuming the PQ result is the fact table? How is that going to work when you switch the parameters from B to A, do a refresh and then when coming back to B and do a refresh the values you entered for the A supplier will disappear.

[#5]

Yes, that's not a problem. The user chooses a supplier, then fills in values, then copies the results. So it's okay for the values to disappear once the calculation has been made.
To make things even stranger, the exact same file that gave #REF errors yesterday, works fine now...

[#6]

@bartvana 

Then your problem has been solved.

[#7]

@Yea_So Well, kind of. I don't really like stuff that doesn't work and then magically starts to work, doesn't give me a lot of confidence... But, yes, short term, the problem seems to have disappeared...

[#8]

@bartvana 

I know that feeling, you can try a different angle, instead of adding columns to the power query resultant, you can create a table where the user will input the parameter values and do a SUMIF power query style:

https://youtu.be/twgcfuJx1sU

[#9]

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

[#10]

Hi @Yea_So 

Just in case, there's an easier and more efficient way to perform the SUMIFS done in the above video

[#11]

Hi @L z. 

any input from you is appreciated.  Thank you

[#12 · Accepted solution]

@L z. & @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.

[#13]

@Yea_So 2 options in the attached file

[#14]

@bartvana 

Well there you have it @bartvana courtesy of @L z. your suggested solution.  Thank you @L z. .

[#15]

OK, thank you!