How can I produce a regular Excel formula as the result of a query?

Question

I have a large and complicated Excel query, which works as desired.

However, I'm implementing some real-time data-validation features (i.e. not requiring a data refresh), and I need to have a regular excel formula in one of the columns of my query results.

This formula would perform a real-time data comparison using other sheets in the workbook, intentionally independent from the query itself.

Can I add a custom column with no value?
I assume the values null or "" would overwrite any pre-existing data, so I couldn't just enter the excel formula into the results table after running the query (the formula wouldn't persist between refreshes).

For testing, I tried adding a custom column with the value "=5+2", just to see how it would behave.

let
    Source = Excel.CurrentWorkbook(){[Name="tblInvoicesCategorized"]}[Content],

    /* ... perform numerous query actions ... */

    // Use "=5+2" as a test formula
    #"Added Custom13" = Table.AddColumn(#"Added Custom12", "Stale Data", each "=5+2"),

    /* ... perform numerous query actions ... */

in
    #"Changed Type"

The query did output the formula as the value of the cells in the column, but Excel did not automatically execute the formulas.
I still had to manually place the cursor into a cell and press enter, to get it to execute the formula.

Manual entry of the formula, or even manual execution of a macro, is undesirable for this workbook.

Is there a way for me to have a regular formula in this query column, and have it automatically persist between data refreshes?

Answer 1

 #"Added Custom13" = Table.AddColumn(#"Added Custom12", "Stale Data", each "=5+2"),

This is not working the way you expect it because you are returning a string literal: "=5+2"

The = is implied and quotes are not necessary

 #"Added Custom13" = Table.AddColumn(#"Added Custom12", "Stale Data", each 5+2),

This will return 7 without requiring manual refresh. However, you are limited to Power Query Formulas which have a different set of functions than the standard excel formulas.

https://msdn.microsoft.com/library/1ed840b1-7e20-4419-ad2f-d82054c9b2ab

This can make accessing data from your worksheet a chore, but it is possible. Some tips can be found here:

How can I reference a cell's value in a Power Query

Answer 2

a workaround is to trigger the refreshall on cell change by some VBA code ...

this was my case, hope it helps...

Private Sub Worksheet_Change(ByVal Target As Range)

  '
  '  Dim lTest As Long, cn As WorkbookConnection
  '  On Error Resume Next
  '  For Each cn In ThisWorkbook.Connections
  '      lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
  '  If Err.Number <> 0 Then
  '      Err.Clear
  '      Exit For
  '  End If
  '  If lTest > 0 Then cn.Refresh
  '  Next cn




    If Intersect(Target, Me.Range("datachange")) Is Nothing Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop

    'Application.Goto Reference:="Tum_Santiyelerin_Satinalinan_Malzemeleri"
    Range("Tum_Santiyelerin_Satinalinan_Malzemeleri").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre"
    'Range("Filtre").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre_Malzeme"
    Range("Filtre_Malzeme").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre_Proje"
    Range("Filtre_Proje").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre_Firma"
    Range("Filtre_Firma").ListObject.QueryTable.Refresh BackgroundQuery:=False



    Application.Goto Reference:="Tum_Santiyelerin_Satinalinan_Malzemeleri"
    ActiveWorkbook.RefreshAll
    Application.EnableEvents = True

Answer 3

You cannot output a column which will directly call Excel formulas. You can use a different column in the worksheet that will call the Excel formula on the and reference a cell from the table that Power Query outputs. This cell will refresh when the Power Query table is refreshed.

Answer 4

I realize this is an old thread, but wanted to give my answer in-case anyone else comes across it like I did. I came across this solution by accident, so I cannot say if it is by design or a bug that will be resolved with some future release.

Rather than trying to include the formula in the Query results, if you add a column with your formula directly to the end of the output table and choose the option "Overwrite all cells in this column with this formula," then when you refresh your query the formula will populate for all rows of the table and will resolve like a normal worksheet formula.

Answer 5

I know the post is a little old but I had the same problem and couldn't find a solution for it.

You can actually do this with a little trick with a macro. I have a power query with 18 different queries and some need a formula. For performance boost I wrote a little vba to refresh everything faster and save the document after the refresh. It looks like this:

Sub Aktualisieren()

    Application.Calculation = xlCalculationManual
    ActiveWorkbook.RefreshAll
    Application.Calculation = xlAutomatic

      On Error GoTo ErrorHandler
      ActiveWorkbook.Save
      Exit Sub

      ErrorHandler: MsgBox "You can't save the file right now! This isn't a bug!", vbInformation  Exit Sub

End Sub

Now to my problem I got some formulas in my query like this on Query with formula

If you add the following code to my Sub Aktualisieren(), the formula will be automatically activated:

Sub Aktualisieren()

    Application.Calculation = xlCalculationManual
    ActiveWorkbook.RefreshAll

    ThisWorkbook.Sheets("Report").Range("AM2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP([@Arbeitsplatz],Verzeichnis!R3C5:R26C6,2,FALSE),"""")"
    ThisWorkbook.Sheets("Report").Range("AM2").Select
    Selection.AutoFill Destination:=Range("Report[Prio Anlage]")

    Application.Calculation = xlAutomatic
      On Error GoTo ErrorHandler
      ActiveWorkbook.Save
      Exit Sub

      ErrorHandler: MsgBox "You can't save the file right now! This isn't a bug!", vbInformation 
        Exit Sub

End Sub

I did it with the Macro maker built within Excel. But basically you can copy that code and only need to change ThisWorkbook.Sheets("Report").Range("AM2").Select, your formula "=IFERROR(VLOOKUP([@Arbeitsplatz],Verzeichnis!R3C5:R26C6,2,FALSE),"""")" and the Autofill Destination.

I hope it can help you.