List.Generate()报错：无法将表转文本，求问题原因解析

场景与需求 #

• 初始表：[初始表截图]
• 需求：按年份逐行过滤并转换表数据，通过List.Generate()遍历年份唯一值列表，预期输出[预期结果截图]

正确实现代码 #

主查询 #

let 
Transformed = List.Generate(()=> [Year = List.Min(Years), Function = FilterAndTransform(Year,"Table1")],
                 each [Year] <= List.Max(Years), 
                 each [Year = [Year]+1, Function = FilterAndTransform([Year]+1, "Table1")],
                 each [Function]),
    #"Converted to Table" = Table.FromList(Transformed, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Column1", "PAX", "ASK", "RPK"}, {"Column1.Column1", "Column1.PAX", "Column1.ASK", "Column1.RPK"})
in
    #"Expanded Column1"

自定义函数 FilterAndTransform #

(Year as number, Table as text) =>
let 
    Source = Excel.CurrentWorkbook(){[Name=Table]}[Content],
    Filtered = Table.SelectRows(Source, each ([Column1] = Year)),
    Pivoted = Table.Pivot(Filtered, List.Distinct(Filtered[Column2]), "Column2", "Column3", List.Sum)
in
    Pivoted

报错的修改后代码 #

主查询 #

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transformed = List.Generate(()=> [Year = List.Min(Years), Function = FilterAndTransformM(Year, Source)],
                 each [Year] <= List.Max(Years), 
                 each [Year = [Year]+1, Function = FilterAndTransform([Year]+1, Source)],
                 each [Function])
in
    Transformed

自定义函数 FilterAndTransformM #

(Year as number, Table as table) =>
let 
    Filtered = Table.SelectRows(Table, each ([Column1] = Year)),
    Pivoted = Table.Pivot(Filtered, List.Distinct(Filtered[Column2]), "Column2", "Column3", List.Sum)
in
    Pivoted

报错原因与修正 #

报错提示“无法将表转换为文本”的核心原因：

• 在List.Generate()的迭代步骤中，错误调用了旧函数FilterAndTransform，而非新定义的FilterAndTransformM。
• 旧函数FilterAndTransform的第二个参数要求是文本类型（表名称字符串），但传入的是已加载的Source（表类型），Power Query为匹配参数类型尝试将表转为文本，因此触发错误。

修正方法：将迭代步骤中的FilterAndTransform替换为FilterAndTransformM，确保调用接受表类型参数的新函数：

each [Year = [Year]+1, Function = FilterAndTransformM([Year]+1, Source)]

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