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



## SL22 (May 22, 2020)

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 (May 23, 2020)

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 (May 25, 2020)

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 (May 25, 2020)

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 (May 25, 2020)

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 (May 25, 2020)

SL22 said:


> 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 (May 25, 2020)

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 (May 25, 2020)

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 (May 25, 2020)

Just tested your code and it works!!! 

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


----------



## smozgur (May 25, 2020)

SL22 said:


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


----------



## SL22 (May 22, 2020)

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


----------



## SL22 (May 25, 2020)

Totally, that makes a lot of sense. In my case the column names always change but the order is the same so hence the column locations. I will look into updating the ColumnNames in the Unpivot section as well. Thanks again


----------

