Import Excel file with changing columns

When we import a folder, several things actually happen:

• Find all files
• Generate a calculation template based on the user's operation, and only use this template to read the worksheet
• Execute the calculation template in the previous step for all files
• Expand the worksheet
• Set the type and other steps

The problem you mentioned occurs when "expanding the worksheet". When expanding the worksheet (using Table.ExpandTableColumn), you need to provide the column name, whether the user writes code or selects through the UI.

Solution:

• Check the steps automatically generated by Power Query and delete the steps after expanding the table column
• Write code to collect all column names, and deduplicate and sort the column names (if you need)
• Use this list of stored column names to expand the table

The sample code is as follows:

let
    源 = Folder.Files("C:\Users\Black\Desktop\新建文件夹 (3)"),
    筛选的隐藏文件1 = Table.SelectRows(源, each [Attributes]?[Hidden]? <> true),
    调用自定义函数1 = Table.AddColumn(筛选的隐藏文件1, "转换文件", each 转换文件([Content])),
    重命名的列1 = Table.RenameColumns(调用自定义函数1, {"Name", "Source.Name"}),
    删除的其他列1 = Table.SelectColumns(重命名的列1, {"Source.Name", "转换文件"}), 
    columnNameList = List.Distinct(List.Combine(List.Transform(删除的其他列1[转换文件], Table.ColumnNames))), 
    expandColumn = Table.ExpandTableColumn(删除的其他列1, "转换文件", columnNameList, columnNameList)
in
    expandColumn

data:

1.xlsx

2.xlsx

3.xlsx

result: