Forum Discussion

xyz_timh's avatar
xyz_timh
New Member
3 years ago

Attempt to use Table.ColumnNames() in XLSX import giving error

I want to import an XLSX file in a folder, and I want to avoid using the built-in method.

This works

 

let 
   FilePath = Excel.CurrentWorkbook(){[Name="fp"]}[Content]{0}[TheFilePath],
   Source = Excel.Workbook(File.Contents(FilePath)),
    #"Kept First Rows" = Table.FirstN(Source,1),
    #"Expanded Data" = Table.ExpandTableColumn(#"Kept First Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Name", "Item", "Kind", "Hidden"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",List.Transform(Table.ColumnNames(#"Promoted Headers"),each {_,type text}))
in
    #"Changed Type"

 

 

But the file changes so I do not want to list the columns explicitly. To this end, I am trying to replace this line

 

#"Expanded Data" = Table.ExpandTableColumn(#"Kept First Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11"})

 

with this

 

#"Expanded Data" = Table.ExpandTableColumn(#"Kept First Rows","Data",Table.ColumnNames(#"Kept First Rows"))

 

But I get the error

Expression.Error: The field 'Name' already exists in the record.
Details:
Name=Name
Value=
= Table.Combine(#"Table.FirstN(Source,1)"[data])

 

where Keep first rows look like this

 

Thank you for assistance with this

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    If you remove the "Name" column before you expand the tables, your code should work. Seems like you have a column in the Tables named "Name".

     

    --Nate