PQ Run-time error '1004': [Expression.Error] The Column 'Column2' of the table wasn't found.

[Belinea]

This sub is designed to run a Excel 2019 Power Query that imports, sorts and extracts data as required.

DISCLAIMER.
I have asked this question in a different forum but I did not receive an answer, I have closed that question so it is not an active cross post.

The column names in my code, due to cofidentiality, are fake and only act as place holders for the correct ones.

I created the PQ by recording it as a macro which works perfectly as long as every file has the same name so I have added the file pickerto give the query additional flexability.

I use Excel 2019
Windows 10

When I run the sub I get the following error.

Run-time error '1004': [Expression.Error] The Column 'Column2

debug highlights 

".Refresh BackgroundQuery:=False"

Hovering over any part of that line with the cursor in the VBA editor does not show anything.

If I look at the error message in Querys & Connections it shows the following.

"Expression.Error: The column 'Column2' of the table wasn't found.
Details:
Column2"

I am not experenced in PQ and I am at a loss at to what to do.

On and off I have spent weeks of what little free time I have on this so I hope that someone may help.

Thank you for any assisstance you may offer.

Sub ImportTransformTXT_Rev_25()
   Dim backSlash As String
   backSlash = Application.PathSeparator

   ' Create a FileDialog object as a File Picker
   Dim fd As FileDialog
   Set fd = Application.FileDialog(msoFileDialogFilePicker)

   ' Filter to only allow text files to be selected
   fd.Filters.Add "Text Files", "*.txt"

   ' Shows the File Picker dialog box
   If fd.Show = -1 Then
      Dim FileFullPath As String
      FileFullPath = fd.SelectedItems(1) ' Gets the selected file path
   Else
      Exit Sub ' User cancelled, exit the sub
   End If

   Dim FileName As String
   FileName = Mid$(FileFullPath, InStrRev(FileFullPath, backSlash) + 1)
   ' remove extension and any other dots
   FileName = Replace$(Left$(FileName, InStrRev(FileName, ".") - 1), ".", "")

   Dim FileNameNoSpaces As String
   FileNameNoSpaces = Replace$(FileName, " ", "_")

   ' Add a new query to import and transform the CSV data
   ActiveWorkbook.Queries.Add Name:=FileName, Formula:= _
      "let" & vbCrLf & _
      "    Source = Csv.Document(File.Contents(""" & FileFullPath & """,[Delimiter="";"", Encoding=65001, QuoteStyle=QuoteStyle.None])," & vbCrLf & _
      "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}})," & vbCrLf & _
      "    #""Added Index"" = Table.AddIndexColumn(#""Changed Type"", ""Index"", 1, 1, Int64.Type)," & vbCrLf & _
      "    #""Inserted Merged Column"" = Table.AddColumn(#""Added Index"", ""Merged"", each Text.Combine({[Column3], [Column2]}, """"), type text)," & vbCrLf & _
      "    #""Removed Columns"" = Table.RemoveColumns(#""Inserted Merged Column"",{""Column2"", ""Column3""})," & vbCrLf & _
      "    #""Reordered Columns"" = Table.ReorderColumns(#""Removed Columns"",{""Column1"", ""Merged"", ""Index""})," & vbCrLf & _
      "    #""Filtered Rows"" = Table.SelectRows(#""Reordered Columns"", each ([Column1] = ""Company name"" or [Column1] = ""Complete name"" or [Column1] = ""Credit Card #"" or [Column1] = ""Credit Card Type"" or [Column1] = ""Currency"" or [Column1] = ""email""))," & vbCrLf & _
      "    #""Pivoted Column"" = Table.Pivot(#""Filtered Rows"", List.Distinct(#""Filtered Rows""[Column1]), ""Column1"", ""Merged"")," & vbCrLf & _
      "    #""Reordered Columns1"" = Table.ReorderColumns(#""Pivoted Column"",{""Index"", ""Complete name"", ""email"", ""Credit Card Type"", ""Credit Card #"", ""Currency"", ""Company name""})" & vbCrLf & _
      "in" & vbCrLf & _
      "    #""Reordered Columns1"""

   ' Add a new worksheet to paste the data
   ActiveWorkbook.Worksheets.Add

   ' Create a ListObject to hold the imported data
   Dim lo As ListObject
   Set lo = ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
      "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & FileName & """;Extended Properties=""""" _
      , Destination:=Range("$A$1"))

   With lo.QueryTable
      .CommandType = xlCmdSql
      .CommandText = Array("SELECT * FROM [" & FileName & "]")
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
       lo.DisplayName = FileNameNoSpaces
      .Refresh BackgroundQuery:=False ' Refresh the query without running in the background
   End With

   ' Get the range of the imported data and then unlist the ListObject
   Dim dataRange As Range
   Set dataRange = lo.Range
   lo.Unlist

   ' Hide the 'Queries and Connections' task pane
   Application.CommandBars("Queries and Connections").Visible = False

   ' Clear formatting and borders inside the data range
End Sub

[Belinea]

Thank you for your reply but I find when people just post links to other sites to be completely unhelpful.

It seems to be a trend on forums in general rather than actually providing real help. This is sadly the case on the mainstream Excel forums but I did not expect it to have infected Microsofts own forums.

If I knew what I was doing then I would not need to ask for help and, although it does not seem to be appreciate, I have done darn well to get my code to where it is without help.

There is no point in my reading tech articles on the subject if I do not understand the jargon - hence why people ask for help. I am a person who learns from doing, not reading and as I said in my post "I am not experenced in PQ".

Time used to be where experts liked to impart thier knowledge and their help, notwithstanding the fact that they gave thier time for free (greatly appreciated).

Your reply has not provided any real help so I am unable to mark it as solved.

[ronrsnfld]

It is very difficult for me to tell what is going on without examining a representative CSV file. My best guess is that the referenced CSV file does not have a "Column2". Possibly if you examine the CSV file in a Text Editor, you can see that.

The fix would require re-writing the query so as not to have column headers hard-coded. This can be done in a variety of ways, generally using Table.ColumnNames(#"Previous Step") to access a list of column names in the order shown in the #"Previous Step".

For example, to set all of the columns to type text, one could use:

#"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(#"Previous Step"), each {_, type text}))

modified as needed for a VBA routine.

And so forth.

Or you could extract the column names from the first line of the CSV file in VBA, and then modify the query to use those names appropriately.

[Anonymous]

Hi @Belinea ,
Based on your description, I checked the documentation for the issue in question and the reason that caused Run-time error '1004': [Expression.Error] The Column 'Column2' of the table wasn't found to occur could be due to the VBA code referencing a non-existent range or the VBA code referencing a misspelled or non-existent named range.
You can refer to the following documents, which give more details about the cause and possible workarounds.

VBA Excel - Run-time Error '1004' when trying to assign a value to a Cell - Stack Overflow
date - VBA Excel - Compile Error Object Required - Stack Overflow

Excel VBA Runtime Error 1004 - How to Fix! (spreadsheetplanet.com)

[Solved]: How to Fix Excel “Run-Time Error 1004” in Minutes | Geekflare

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Belinea]

I further apoligise, I discovered how to edit my post but doing so does not remove the gap, this is both annoying and very strange. Sorry again folks, I am going to go sit in the corner of a dark room and rock back and forth.😐

[Belinea]

I apoligise for the large gap in the middle of my post. No idea how that happened and I am unable to find a means of editing this post to remove the gap.