How to avoid Power Query error with empty query from VBA Excel?

Question

I would like to import a text file into Excel filtering just what I want through a VBA macro. The amount of data is large so I use efficently the Power queries. I have a list of several things to filter and process differently and this list could change. So for each "feature" to filter I reload the query in a new sheet. If the filter makes the query empty I get an error from the Power Query that I am not able to skip with:

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False 

Debugging I see that the error comes out between the query creation and the paste to the sheet, see (*) in the code below.

Does somebody know if there is a way to have the number of records into the query in order to be able to use an if statement and skip the paste phase?

The only other idea that I have is to write automatically a row for each feature into the txt file to filter but it is not an elegant method

A thing that I do not understand is that the problem appear using a function, see below, but not using directly a macro. When I use the function the error shown does not appear always but in any case the code finish the function but the main macro stops.

test.txt

946737295   9CE78280    FF  1   5   FF  FF  FF  FF  FF
946737295   9CE78280    C0  FF  0   0   0   0   FF  FF
946737295   9CE68082    C0  4   0   FF  FF  FF  FF  FF

and the macro is:

Function readTxt(input_path As String, Pgn As String, B2 As String, B3 As String) As Boolean

    Dim Wb As Workbook
    Dim Ws As Worksheet
    Dim Conn As WorkbookConnection
    Dim mFormula As String
    Dim query As WorkbookQuery
    
    Set Wb = ActiveWorkbook
    Set Ws = Wb.ActiveSheet
    
    On Error Resume Next
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
       

    mFormula = "let " & _
        "Source = Csv.Document(File.Contents(""" & input_path & """),[Delimiter=""#(tab)"", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & _
        "#""Step1"" = Table.SelectRows(Source, each Text.Contains([Column2], """ & Pgn & """) and [Column5] = """ & B3 & """ and [Column4] = """ & B2 & """)," & _
        "#""Step2"" = Table.RemoveColumns(Step1,{""Column2"", ""Column3"", ""Column4"", ""Column5"", ""Column9"", ""Column10""})" & _
        "in #""Step2"""
    
    Set query = Wb.Queries.Add("test_7", mFormula)

    With Ws.ListObjects.Add(SourceType:=0, Source:= _
         "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & "test_7" & ";Extended Properties=""""", Destination:=Ws.Range("A3"), XlListObjectHasHeaders:=xlYes).QueryTable
         '.ListObject.TotalsRowRange
         .CommandType = xlCmdSql
         .AdjustColumnWidth = False
         .ListObject.Name = "test"
         .CommandText = "SELECT * FROM [" & "test_7" & "]" 
         .Refresh BackgroundQuery:=False
     End With
         
     If Err.Number <> 0 Then
            Err.Clear
     End If
      
      query.Delete
         
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    readTxt = True 'output
    
    On Error GoTo 0

End Function

Sub readTxt()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim Conn As WorkbookConnection
Dim mFormula As String
Dim query As WorkbookQuery

Set Wb = ActiveWorkbook

Dim i As Integer
Dim C3 As String

    On Error Resume Next
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

C3 = "F2"

For i = 1 To 2
       
    If i = 2 Then
        C3 = "FF"
        Sheets.Add After:=ActiveSheet
    End If
    Set Ws = Wb.ActiveSheet

    mFormula = "let " & _
        "Source = Csv.Document(File.Contents(""C:\test.txt""),[Delimiter=""#(tab)"", Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & _
        "#""Step1"" = Table.SelectRows(Source, each Text.Contains([Column2], ""E7"") and [Column3] = """ & C3 & """)" & _
        "in #""Step1"""
        
    Set query = Wb.Queries.Add("Test_text", mFormula)
    
    ' (*) THE ERROR OF POWER QUERY APPEARS HERE
    
    With Ws.ListObjects.Add(SourceType:=0, Source:= _
         "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & "Test_text" & ";Extended Properties=""""", Destination:=Ws.Range("A3"), XlListObjectHasHeaders:=xlYes).QueryTable
         .CommandType = xlCmdSql
         .AdjustColumnWidth = False
         .ListObject.Name = "test"
         .CommandText = "SELECT * FROM [" & "Test_text" & "]"
         .Refresh BackgroundQuery:=False
     End With
     
     query.Delete
 
Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    On Error GoTo 0
      
End Sub

Thanks, Ruggero

Answer 1

You can check if a step (table) has some specific columns using this code:

let Source = Csv.Document(File.Contents("C:\temp\test.txt"),[Delimiter=";", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    #"Step1" = Table.SelectRows(Source, each Text.Contains([Column2], "E7") and [Column3] = "F1"),
    result_error = "Some error",
    check_columns = Table.HasColumns(#"Step1", {"Column2", "Column3"}),
    result = if check_columns = true then #"Step1" else result_error
in result

See the check_columns step and the conditional result