Run-time error '1004':[ Expression.Error] The name 'Source' wasn't recognized. Make sure it's spelled correctly

SL22

New Member
Joined
May 22, 2020
Messages
6
Office Version
2019
Platform
Windows
I'm trying to add Power Query code to VBA but I'm getting an error that the "Source" wasn't recognized.

The name 'Source' wasn't recognized. Make sure it's spelled correctly.

I've been trying to do different spellings or playing with the commas but I'm still getting that error. Would need a second pair of eyes and any suggestions would be extremely helpful. Thanks.

VBA Code:
 ActiveSheet.ListObjects("Table1").TableStyle = ""
    ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source, {{Table.ColumnNames(Table1){0}, " & _
        "Int64.Type}, { Table.ColumnNames(Table1){1}, type text}, { Table.ColumnNames(Table1){2}, type text}, { Table.ColumnNames(Table1){3}, type text}, { Table.ColumnNames(Table1){4}, type text}, { Table.ColumnNames(Table1){5}, type text}, { Table.ColumnNames(Table1){6}, Int64.Type}, { Table.ColumnNames(Table1){7}, " & _
        "type datetime}, { Table.ColumnNames(Table1){8}, type number}, { Table.ColumnNames(Table1){9}, type number}, { Table.ColumnNames(Table1){10}, type number}, { Table.ColumnNames(Table1){11}, type number}, { Table.ColumnNames(Table1){12}, type any}, { Table.ColumnNames(Table1){13}, type any}, { Table.ColumnNames(Table1){14}, type number}, { Table.ColumnNames(Table1){15}, type number}, { Table.ColumnNames(Table1){16}, type any}, { Table.ColumnNames(Table1){17}, type any}, { Table.ColumnNames(Table1){18}, " & _
        "type any},{ Table.ColumnNames(Table1){19}, type any}, { Table.ColumnNames(Table1){20} , type any}, { Table.ColumnNames(Table1){21}, type any}, { Table.ColumnNames(Table1){22}, type number}, { Table.ColumnNames(Table1){23}, type any}, { Table.ColumnNames(Table1){24}, type number}, { Table.ColumnNames(Table1){25}, type number}, { Table.ColumnNames(Table1){25}, type text}, { Table.ColumnNames(Table1){26}, type text}, { Table.ColumnNames(Table1){27}, type any}, { Table.ColumnNames(Table1){28}, type any}, { Table.ColumnNames(Table1){29}, " & _
        "type any}, { Table.ColumnNames(Table1){30}, type any}, { Table.ColumnNames(Table1){31}, type any}, { Table.ColumnNames(Table1){32}, {Table.ColumnNames(Table1){33} ,type any}, {Table.ColumnNames(Table1){34}, type any}, { Table.ColumnNames(Table1){35}, type any}, { Table.ColumnNames(Table1){36}, type any}, { Table.ColumnNames(Table1){37}, type any}, { Table.ColumnNames(Table1){38}, type any}, { Table.ColumnNames(Table1){39}, type any}, { Table.ColumnNames(Table1){40}, type number}, { Table.ColumnNames(Table1){41}, " & _
        "type number}, { Table.ColumnNames(Table1){42}, type number}, {Table.ColumnNames(Table1){43}, type any}, { Table.ColumnNames(Table1){44}, type any}, { Table.ColumnNames(Table1){45}, type number}, { Table.ColumnNames(Table1){46}, Int64.Type}, { Table.ColumnNames(Table1){47}, type number}, { Table.ColumnNames(Table1){48}, type any}, { Table.ColumnNames(Table1){49}, Int64.Type}, { Table.ColumnNames(Table1){50}, type any}, { Table.ColumnNames(Table1){51}, type number}, { Table.ColumnNames(Table1){52}, type text}, { Table.ColumnNames(Table1){53}, " & _
        "type text}, { Table.ColumnNames(Table1){54}, type number}, { Table.ColumnNames(Table1){55}, type text}, { Table.ColumnNames(Table1){56}, type text}, { Table.ColumnNames(Table1){57}, type any}, { Table.ColumnNames(Table1){58}, type any}, { Table.ColumnNames(Table1){59} , type any}, { Table.ColumnNames(Table1){60}, type any}, { Table.ColumnNames(Table1){61}, type any}, { Table.ColumnNames(Table1){62}, type any}, { Table.ColumnNames(Table1){63}, type any}, { Table.ColumnNames(Table1){64}, type any}, { Table.ColumnNames(Table1){65}, " & _
        "type any},  { Table.ColumnNames(Table1){66}, type number}, { Table.ColumnNames(Table1){67}, type number}, { Table.ColumnNames(Table1){68}, type number}, { Table.ColumnNames(Table1){69}, type number}, { Table.ColumnNames(Table1){70}, type number}, { Table.ColumnNames(Table1){71}, type number}, { Table.ColumnNames(Table1){72}, type number}, { Table.ColumnNames(Table1){73}, type any}, { Table.ColumnNames(Table1){74}, type any}, { Table.ColumnNames(Table1){75}, type number}, { Table.ColumnNames(Table1){76}, type number}, { Table.ColumnNames(Table1){77}, " & _
        "Int64.Type}, { Table.ColumnNames(Table1){78}, type any}, { Table.ColumnNames(Table1){79}, Int64.Type}, { Table.ColumnNames(Table1){80}, type number}, { Table.ColumnNames(Table1){81}, type any}, { Table.ColumnNames(Table1){82}, type any}, { Table.ColumnNames(Table1){83}, type any}, { Table.ColumnNames(Table1){84} , type any}, { Table.ColumnNames(Table1){85}, Int64.Type}, { Table.ColumnNames(Table1){86}, type text}, { Table.ColumnNames(Table1){87}, type any}, { Table.ColumnNames(Table1){88}, type any}, { Table.ColumnNames(Table1){89}, " & _
        "type any}, { Table.ColumnNames(Table1){90}, type number}, { Table.ColumnNames(Table1){91}, type any}, { Table.ColumnNames(Table1){92}, type any}, { Table.ColumnNames(Table1){93}, type any}, { Table.ColumnNames(Table1){94}, type any}, { Table.ColumnNames(Table1){95}, type number}, { Table.ColumnNames(Table1){96}, type any}, { Table.ColumnNames(Table1){97}, type any}, { Table.ColumnNames(Table1){98}, type any}, { Table.ColumnNames(Table1){99}, type any}, { Table.ColumnNames(Table1){100}, type any}, { Table.ColumnNames(Table1){101}, " & _
        "type any}, { Table.ColumnNames(Table1){102}, type any}, { Table.ColumnNames(Table1){103}, type any}, { Table.ColumnNames(Table1){104}, type any}, { Table.ColumnNames(Table1){105}, type any}, { Table.ColumnNames(Table1){106}, type any}, { Table.ColumnNames(Table1){107}, " & _
        "type number},  {Table.ColumnNames(Table1){108}, type any}})," & Chr(13) & "" & Chr(10) & "    #""Unpivoted Other Columns"" = Table.UnpivotOtherColumns(#""Changed Type"", {""DPT "", ""DPT-DESC                 "", ""EMP-CD"", ""FIRST NAME          "", ""LAST NAME           "", ""S.S.N.     "", ""PAY-DT"", ""Pay Date"", ""HR-RATE ""}, ""Attribute"", ""Value"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Unpivoted Other Columns"""
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
933
Welcome to the Board!

First thing I can see, take a look at the Table.ColumnNames(Table1){32}. Missing column type and missing closing curly bracket in the M-Code.
 

SL22

New Member
Joined
May 22, 2020
Messages
6
Office Version
2019
Platform
Windows
Good catch :) Didn't notice that before.

So I think that solved the orignal error but now I'm getting a new error:
Rich (BB code):
[Expression.Error] A cyclic reference was encountered during evaluation.
Any thoughts?
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
933
How about posting some sample data with table headers by using XL2BB?
 

SL22

New Member
Joined
May 22, 2020
Messages
6
Office Version
2019
Platform
Windows
Would I be able to take a screenshot instead of the column headers with dummy data? The company I work for only allows their approved add on's and is a process to get new one's added.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
933
Would I be able to take a screenshot instead of the column headers with dummy data? The company I work for only allows their approved add on's and is a process to get new one's added.
No need data. If you could post column headers as text, then anybody who thinks that they can help / test can copy and paste headers.
(In fact, you should never include sensitive data in the samples)
 

SL22

New Member
Joined
May 22, 2020
Messages
6
Office Version
2019
Platform
Windows
There are a lot of columns:
DPT DPT-DESC EMP-CDFIRST NAME LAST NAME S.S.N. PAY-DTHR-RATE REG-HRS OT-HRS SICK-HRS HOL-HRS CVAC-HRS SHIFT-HRS RATE OTHER1-HRSRATE 2REG-EARN OT-EARN SICK-EARN HOL-EARN C3VAC-EARN LONG-EARN SHIFT-EARNHOLOT-EARNBIRTH-EARNPERSN-EARNOTHER1-DLRTRANS-ALW UNIF-ALW FICA FED-TAX ST-TAX CITY-TAX DIS YONK-TAX EIC UNION-DUE TSA CR-UN LOAN UNIFORM MED-INS GARNISH PENS-FUND INIT-FEE DEN PRE TAMISC1-DED C4MISC2-DED C5INS-PRE-TXAFL-AFT-TXAFL-PRE-TXUNION DUESSPOUSE INSLIFE INSURMASS. MUTUROTH 401K 401k loan1MISC-DED7 MISC-DED8 INS CATCH MISC-DED10MISC-DED11MISC-DED12MISC-DED13MISC-DED14MISC-DED15PFL MISC-DED17
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
933
Ok, second problem to fix (I should have seen it before actually): Look at your VBA code: It is setting Source as the table name:

Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]

However, it is then trying to refer it as Table1 in the Table.TransformColumnTypes (Changed Type assignment).

#""Changed Type"" = Table.TransformColumnTypes(Source, {{Table.ColumnNames(Table1){0} ....

Table1 should be Source or Source should be Table1 to refer to the table variable assigned in the first step. It is easier to change 2 Source occurrences in the code instead 109 Table1.

Third problem - You are using { Table.ColumnNames(Table1){25}, type text} twice.

Your code should be like below to fix these two problems:

VBA Code:
ActiveSheet.ListObjects("Table1").TableStyle = ""
    ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Table1 = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table1, {{Table.ColumnNames(Table1){0}, " & _
        "Int64.Type}, { Table.ColumnNames(Table1){1}, type text}, { Table.ColumnNames(Table1){2}, type text}, { Table.ColumnNames(Table1){3}, type text}, { Table.ColumnNames(Table1){4}, type text}, { Table.ColumnNames(Table1){5}, type text}, { Table.ColumnNames(Table1){6}, Int64.Type}, { Table.ColumnNames(Table1){7}, " & _
        "type datetime}, { Table.ColumnNames(Table1){8}, type number}, { Table.ColumnNames(Table1){9}, type number}, { Table.ColumnNames(Table1){10}, type number}, { Table.ColumnNames(Table1){11}, type number}, { Table.ColumnNames(Table1){12}, type any}, { Table.ColumnNames(Table1){13}, type any}, { Table.ColumnNames(Table1){14}, type number}, { Table.ColumnNames(Table1){15}, type number}, { Table.ColumnNames(Table1){16}, type any}, { Table.ColumnNames(Table1){17}, type any}, { Table.ColumnNames(Table1){18}, " & _
        "type any},{ Table.ColumnNames(Table1){19}, type any}, { Table.ColumnNames(Table1){20} , type any}, { Table.ColumnNames(Table1){21}, type any}, { Table.ColumnNames(Table1){22}, type number}, { Table.ColumnNames(Table1){23}, type any}, { Table.ColumnNames(Table1){24}, type number}, { Table.ColumnNames(Table1){25}, type text}, { Table.ColumnNames(Table1){26}, type text}, { Table.ColumnNames(Table1){27}, type any}, { Table.ColumnNames(Table1){28}, type any}, { Table.ColumnNames(Table1){29}, " & _
        "type any}, { Table.ColumnNames(Table1){30}, type any}, { Table.ColumnNames(Table1){31}, type any}, { Table.ColumnNames(Table1){32}, type any}, {Table.ColumnNames(Table1){33} ,type any}, {Table.ColumnNames(Table1){34}, type any}, { Table.ColumnNames(Table1){35}, type any}, { Table.ColumnNames(Table1){36}, type any}, { Table.ColumnNames(Table1){37}, type any}, { Table.ColumnNames(Table1){38}, type any}, { Table.ColumnNames(Table1){39}, type any}, { Table.ColumnNames(Table1){40}, type number}, { Table.ColumnNames(Table1){41}, " & _
        "type number}, { Table.ColumnNames(Table1){42}, type number}, {Table.ColumnNames(Table1){43}, type any}, { Table.ColumnNames(Table1){44}, type any}, { Table.ColumnNames(Table1){45}, type number}, { Table.ColumnNames(Table1){46}, Int64.Type}, { Table.ColumnNames(Table1){47}, type number}, { Table.ColumnNames(Table1){48}, type any}, { Table.ColumnNames(Table1){49}, Int64.Type}, { Table.ColumnNames(Table1){50}, type any}, { Table.ColumnNames(Table1){51}, type number}, { Table.ColumnNames(Table1){52}, type text}, { Table.ColumnNames(Table1){53}, " & _
        "type text}, { Table.ColumnNames(Table1){54}, type number}, { Table.ColumnNames(Table1){55}, type text}, { Table.ColumnNames(Table1){56}, type text}, { Table.ColumnNames(Table1){57}, type any}, { Table.ColumnNames(Table1){58}, type any}, { Table.ColumnNames(Table1){59} , type any}, { Table.ColumnNames(Table1){60}, type any}, { Table.ColumnNames(Table1){61}, type any}, { Table.ColumnNames(Table1){62}, type any}, { Table.ColumnNames(Table1){63}, type any}, { Table.ColumnNames(Table1){64}, type any}, { Table.ColumnNames(Table1){65}, " & _
        "type any},  { Table.ColumnNames(Table1){66}, type number}, { Table.ColumnNames(Table1){67}, type number}, { Table.ColumnNames(Table1){68}, type number}, { Table.ColumnNames(Table1){69}, type number}, { Table.ColumnNames(Table1){70}, type number}, { Table.ColumnNames(Table1){71}, type number}, { Table.ColumnNames(Table1){72}, type number}, { Table.ColumnNames(Table1){73}, type any}, { Table.ColumnNames(Table1){74}, type any}, { Table.ColumnNames(Table1){75}, type number}, { Table.ColumnNames(Table1){76}, type number}, { Table.ColumnNames(Table1){77}, " & _
        "Int64.Type}, { Table.ColumnNames(Table1){78}, type any}, { Table.ColumnNames(Table1){79}, Int64.Type}, { Table.ColumnNames(Table1){80}, type number}, { Table.ColumnNames(Table1){81}, type any}, { Table.ColumnNames(Table1){82}, type any}, { Table.ColumnNames(Table1){83}, type any}, { Table.ColumnNames(Table1){84} , type any}, { Table.ColumnNames(Table1){85}, Int64.Type}, { Table.ColumnNames(Table1){86}, type text}, { Table.ColumnNames(Table1){87}, type any}, { Table.ColumnNames(Table1){88}, type any}, { Table.ColumnNames(Table1){89}, " & _
        "type any}, { Table.ColumnNames(Table1){90}, type number}, { Table.ColumnNames(Table1){91}, type any}, { Table.ColumnNames(Table1){92}, type any}, { Table.ColumnNames(Table1){93}, type any}, { Table.ColumnNames(Table1){94}, type any}, { Table.ColumnNames(Table1){95}, type number}, { Table.ColumnNames(Table1){96}, type any}, { Table.ColumnNames(Table1){97}, type any}, { Table.ColumnNames(Table1){98}, type any}, { Table.ColumnNames(Table1){99}, type any}, { Table.ColumnNames(Table1){100}, type any}, { Table.ColumnNames(Table1){101}, " & _
        "type any}, { Table.ColumnNames(Table1){102}, type any}, { Table.ColumnNames(Table1){103}, type any}, { Table.ColumnNames(Table1){104}, type any}, { Table.ColumnNames(Table1){105}, type any}, { Table.ColumnNames(Table1){106}, type any}, { Table.ColumnNames(Table1){107}, " & _
        "type number},  {Table.ColumnNames(Table1){108}, type any}})," & Chr(13) & "" & Chr(10) & "    #""Unpivoted Other Columns"" = Table.UnpivotOtherColumns(#""Changed Type"", {""DPT "", ""DPT-DESC                 "", ""EMP-CD"", ""FIRST NAME          "", ""LAST NAME           "", ""S.S.N.     "", ""PAY-DT"", ""Pay Date"", ""HR-RATE ""}, ""Attribute"", ""Value"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Unpivoted Other Columns"""

However, I can't say anything about the Unpivot step since I am not sure the field names are matching with the actual table headers or not as I can see some space are used in the code.

My suggestion : Just record a new macro again to generate this query and don't alter the code - if you certainly need VBA to do this.
 

SL22

New Member
Joined
May 22, 2020
Messages
6
Office Version
2019
Platform
Windows
Just tested your code and it works!!!

Thank you so much :) You don't realize how much this helped.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
933
Just tested your code and it works!!!

Thank you so much :) You don't realize how much this helped.
Glad to hear it helped!

Note: The only reason using Table.ColumnNames(Table1){index} instead of actual headers as text could be considering the name changes in the headers. However, then in the next step to Unpivot columns, you are using text for headers. It means first step is not compatible with the second step logically (it doesn't break anything, just doesn't make sense). So, if you don't have name change concern then you don't need to use ColumnNames function, but simply use actual names as text. Or if column order is never changed but names might be changed in source then you can also refer the names by using ColumnNames in UnPivotOtherColumns.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,157
Members
405,387
Latest member
michmichmich2020

This Week's Hot Topics

Top