Really struggling and could do with some advice please.
I have an input table in my Excel (2016) worksheet as follows;
I also have a user-defined value in the spreadsheet as follows;
The user updates the highlighted value based on the needs of the analysis. The highlighted cell has been named 'NTValue' in the worksheet.
In my query, I have already introduced a function named "GetValue" to get the value in the cell named "NTValue". This function is;
(rangeName) => Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]
This function runs successfully on its own. If I use GetValue("NTValue"), I am returned with "0.001".
I now want to introduce the GetValue function within a ReplaceValue step in my query i.e. look for all rows where Value1 = "NT" and replace the contents of Value2 with the user-defined NT Value (highlighted in yellow). The target result is as follows;
However, when I use this query, I am returned with an error..
Replace Value Query
= Table.ReplaceValue(#"Table5", each [Value2], each if [Value1] = "NT" then GetValue("NTValue") else [Value2],Replacer.ReplaceValue,{"Value2"})
Error
Formula.Firewall: Query 'New Table' (step 'Replaced Value') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Any ideas?
