如何阻止Power Query导入CSV时自动添加多余空列？

问题核心就是你代码里硬写了Columns=7，强制指定了列数——当CSV实际列数少于7时，Power Query会自动补空列；列数多于7时又会截断数据。要实现自动适配实际列数，只需要做以下修改：

修改步骤 #

• 删掉Csv.Document参数里的Columns=7，让Power Query自动识别CSV的实际列数
• 后续的列处理逻辑无需调整，因为本来就是基于Table.ColumnNames(Source)动态获取列名，天然支持动态列数

修改后的完整代码 #

let
    FilePath = Excel.CurrentWorkbook(){[Name="fp"]}[Content]{0}[TheFilePath],
    Source = Csv.Document(File.Contents(FilePath),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,List.Transform(Table.ColumnNames(Source),each {_,type text})),

    BulkReplaceSyntac = fBulkReplacePart(#"Changed Type", MyFindReplace, Table.ColumnNames(#"Changed Type")),
    #"Replaced Value" = Table.ReplaceValue(BulkReplaceSyntac,"#(00A0)","",Replacer.ReplaceText,Table.ColumnNames(BulkReplaceSyntac)),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(0087)","",Replacer.ReplaceText,Table.ColumnNames(#"Replaced Value")),
    #"Replaced Null" = Table.ReplaceValue(#"Replaced Value1",null,"",Replacer.ReplaceValue,Table.ColumnNames(#"Replaced Value1")),

    #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Null", [PromoteAllScalars=true]),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Promoted Headers", Table.ColumnNames(#"Promoted Headers"))
in
    #"Removed Errors"

补充说明 #

取消Columns参数后，Csv.Document会自动读取CSV的表头行来确定列数，后续的类型转换、文本替换等操作都是基于动态获取的列名列表执行，完全适配CSV列数的变化，不会再生成多余的空列。

内容的提问来源于stack exchange，提问作者Einarr