excel - 如何解决 Power BI 错误 - key 与表中的任何行都不匹配

Question

我正在尝试将多个 Excel 文件加载(组合)到 Power BI(2019 年 10 月版)中。每个文件只有一张。每个工作表都有 1 个范围，每个范围在所有文件中都具有相同的架构。 (但工作表名称不同。)示例工作表名称为“200704”。

这是我的步骤:

获取数据\文件夹\连接

指定文件夹路径

合并和装载

选择其中一个文件作为我的示例文件；单击文件名作为
我的参数1；点击确定

单击“确定”后，光标会旋转一会儿，然后停止。什么都没发生。所以，我去编辑查询\编辑查询。我的数据查询上有一个警告符号，内容如下:

An error occurred in the 'Transform File' query. Expression.Error: The key didn't match any rows in the table.

Details: Key = Item=200704 Kind=Sheet Table=[Table]

如何解决此错误？

如果有帮助，Power BI 会为我生成 5 个查询，其结构为:

从数据转换文件 [2]

帮助查询 [3]

参数1(示例文件)

示例文件

转换文件

转换示例文件

其他查询 [1]

数据

有趣的是，如果它有助于诊断问题，如果我设置示例文件 = 第一个文件，或者如果我手动将示例文件设置为我的第一个文件，对话框中会抛出以下错误，但它不会显示哪个查询出错当我尝试查看/编辑查询时。

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] The key didn't match any rows in the table..'.

而且，可以肯定的是，当我尝试单独(通过 Excel 连接)加载此文件(或文件夹中的任何文件)时，它会成功加载。所以，我的文件夹连接中的 M 代码一定有问题。

Answer 1

我找出了问题的原因和解决方案。问题是我的模板查询中的行被错误地引用(即模板查询和常规查询之间的主键错误，并且它具有工作表名称的硬编码)。为了解决这个问题，我必须删除模板查询表中除数据列之外的所有其他列，如 here 所述。 . (奇怪的是，没有关于合并多个 Excel 文件的 MS 文档讨论这个非常重要的步骤。)

为了比较，这是我以前的(不正确的)M代码:

转换示例文件:

let
    Source = Excel.Workbook(Parameter1, null, true),
    #"Sample_Sheet" = Source{[Item="sample",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Sample_Sheet", [PromoteAllScalars=true])
in
    #"Promoted Headers"

测试:

let
    Source = Folder.Files("C:\some folder path"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"ID", type text}, {"Name", type text}})
in
    #"Changed Type"

这是我的新(正确)代码:

转换示例文件:

let
    Source = Excel.Workbook(Parameter1, null, true),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Name", "Item", "Kind", "Hidden"}),
    Data = #"Removed Columns"{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"Name", type text}})
in
    #"Changed Type"

测试:

let
    Source = Folder.Files("C:\some folder path"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
    #"Expanded Table Column1"

请注意新模板查询中的“已删除列”步骤。这是解决关键问题的“秘方”。另请注意，我在模板查询中的“数据”步骤(即“提升的标题”和“更改的类型”)之后保留了所有默认步骤。这是因为我所有的工作表都具有相同的架构。如果这不是真的，那么我需要将这些步骤移至常规查询。