Power Query自定义函数调用报错：无法将Function转为Table类型

问题定位 #

错误出现在函数的第3个步骤#"Removed Top Rows"：

#"Removed Top Rows" = Table.Skip(#"Table.PromoteHeaders",1),

这里错误引用了内置函数名Table.PromoteHeaders，而非上一步生成的步骤名#"Promoted Headers"。Table.PromoteHeaders是Power Query的函数类型，而Table.Skip需要接收表类型参数，因此触发“无法将Function转换为Table”的类型不匹配错误。

修正后的完整函数 #

let HighFunction = (InTable as table) =>
let Source = InTable,
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Removed Top Rows" = Table.Skip(#"Promoted Headers",1),  // 修正此处的引用
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] = "Cashflows by Tranche")),
    #"Filled Down1" = Table.FillDown(#"Filtered Rows",{"Column2"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down1", each ([Column3] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Column1"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 then "Tranche" else [Column2]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index", "Column2"}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Columns1", [PromoteAllScalars=true]),
    #"Filtered Rows2" = Table.SelectRows(#"Promoted Headers1", each ([Date] <> "Date")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows2", {"Date", "Tranche"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Date]), "Date", "Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Period End Date"}})
in
    #"Renamed Columns"
in HighFunction

验证说明 #

修正后，Table.Skip将正确接收上一步#"Promoted Headers"生成的表，后续步骤的类型匹配问题即可解决，调用HighFunction(#"inpTable")时就能正常处理输入表。

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