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

[SL22]

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.

 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"""

 

[smozgur]

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]

Good catch Didn't notice that before.

So I think that solved the orignal error but now I'm getting a new error:

[Expression.Error] A cyclic reference was encountered during evaluation.

Any thoughts?

 

[smozgur]

How about posting some sample data with table headers by using XL2BB?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[SL22]

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]

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]

There are a lot of columns:

DPT

DPT-DESC

EMP-CD

FIRST NAME

LAST NAME

S.S.N.

PAY-DT

HR-RATE

REG-HRS

OT-HRS

SICK-HRS

HOL-HRS

C

VAC-HRS

SHIFT-HRS

RATE

OTHER1-HRS

RATE 2

REG-EARN

OT-EARN

SICK-EARN

HOL-EARN

C3

VAC-EARN

LONG-EARN

SHIFT-EARN

HOLOT-EARN

BIRTH-EARN

PERSN-EARN

OTHER1-DLR

TRANS-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 TA

MISC1-DED

C4

MISC2-DED

C5

INS-PRE-TX

AFL-AFT-TX

AFL-PRE-TX

UNION DUES

SPOUSE INS

LIFE INSUR

MASS. MUTU

ROTH 401K

401k loan1

MISC-DED7

MISC-DED8

INS CATCH

MISC-DED10

MISC-DED11

MISC-DED12

MISC-DED13

MISC-DED14

MISC-DED15

PFL

MISC-DED17

 

[smozgur]

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:

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]

Just tested your code and it works!!!

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

 

[smozgur]

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.