Jul 14 2021 10:53 AM - last edited on Nov 09 2023 11:09 AM by
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.
Jul 14 2021 07:45 PM
cannot be done that way, please provide a sample file since power query is a complicated function to explain.
cheers
Jul 14 2021 09:30 PM
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.
Jul 14 2021 10:04 PM
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.
Jul 14 2021 10:19 PM
Jul 14 2021 10:20 PM
Jul 14 2021 10:55 PM
@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...
Jul 14 2021 11:01 PM
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:
Jul 15 2021 12:13 AM
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
Jul 15 2021 12:16 AM
Hi @Yea_So
Just in case, there's an easier and more efficient way to perform the SUMIFS done in the above video
Jul 15 2021 12:25 AM
Jul 15 2021 01:13 AM
Solution
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.
Jul 15 2021 01:19 AM
@Yea_So 2 options in the attached file
Jul 15 2021 01:24 AM
Jul 15 2021 01:13 AM
Solution
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.