Home We cannot convert a value of type List to type Text when importing XLSX file Power Query

Questions

We cannot convert a value of type List to type Text when importing XLSX file Power Query

February 23, 2023

I am trying to import an xlsx file from a folder.

I have, which 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 is dynamic in that it keeps getting replaced, and the number of columns keeps changing.

So I am trying to replace this.

#"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(Source,"Data",{Table.ColumnNames(Source)})

I get the error

Expression.Error: We cannot convert a value of type List to type Text.
Details:
    Value=[List]
    Type=[Type]

Image of Source

How to resolve this error?

>Solution :

If you are trying to expand the Data column, this should work on column names dynamically

List = List.Union(List.Transform(#"Kept First Rows"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Kept First Rows", "Data", List,List)

or to pre-append Data.

List = List.Union(List.Transform(#"Kept First Rows"[Data], each Table.ColumnNames(_))),
List2 = List.Transform (List, each "Data."&_),
#"Expanded Data" = Table.ExpandTableColumn(#"Kept First Rows", "Data", List,List2)