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)