Forum Discussion
Complex Excel Xlookup values not correctly retrieved by Power Query
Hi MichaelPercival -You're correct that this issue likely arises because Power Query doesn't retrieve the displayed value of a cell that contains a formula, but rather tries to recompute it based on its internal logic.
Copy the range containing the XLOOKUP formulas.
Paste the values back into the same range using Paste Special > Values Only.
Load this range into Power Query. Since the cells now contain static values, Power Query will retrieve them without reinterpreting the formula.There’s no setting in Power Query to "force retrieve only the displayed value" without pre-processing the data in Excel.
Hope this helps.
Super UserHi MichaelPercival -You're correct that this issue likely arises because Power Query doesn't retrieve the displayed value of a cell that contains a formula, but rather tries to recompute it based on its internal logic.
Copy the range containing the XLOOKUP formulas.
Paste the values back into the same range using Paste Special > Values Only.
Load this range into Power Query. Since the cells now contain static values, Power Query will retrieve them without reinterpreting the formula.
There’s no setting in Power Query to "force retrieve only the displayed value" without pre-processing the data in Excel.
Hope this helps.
Thanks for this. Replacing the values isn't a solution to my specific system, but very valuable to understand the issue. Thanks, rajendraongole1 !
Michael