Power Query循环引用错误排查及周度销售拆分需求求助

问题定位 #

你遇到的Expression.Error: A cyclic reference was encountered during evaluation错误，根源是在添加Full Period Check自定义列时，子查询里直接引用了当前查询本身（#"FM Cleaned data PTD"），Power Query计算每行自定义列时会反复调用整个查询，形成循环。另外子查询硬编码了2023-01，无法动态匹配每行对应的财年周期，逻辑也冗余。

解决思路 #

1. 先单独计算每个财年周期（FM Fiscal FY Y+P）的报表完整性，生成一个周期校验表
2. 将校验表与主数据合并，直接匹配每行对应的周期校验结果，彻底避免循环引用

修改后的完整代码 #

let
    Source = Folder.Files("Source"),
    #"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"))),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Table Column1",{"Column10", "Source.Name", "Trudeau", "Column2", "Column4", "Column5"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Source.Name", "XXXX", "Column2", "Column4", "Column5", "Column10"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns", "Source.Name", "Source.Name - Copy"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Duplicated Column",{"Source.Name", "Source.Name - Copy", "XXXX", "Column2", "Column4", "Column5", "Column10"}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Reordered Columns1", {{"Column2", each Text.AfterDelimiter(_, "-"), type text}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Extracted Text After Delimiter", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Top Rows" = Table.Skip(#"Removed Blank Rows",9),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ("" <> "")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"2023 XXXX Report ver 4.0 WE 11.18.2023 dtd 11.20.2023.xlsx", "Raw file name"}, {"2023 XXXX Report ver 4.0 WE 11.18.2023 dtd 11.20.2023.xlsx_1", "End week date"}, {"", "Description"}}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Renamed Columns", {{"End week date", each Text.BetweenDelimiters(_, "WE", "dtd"), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Between Delimiters",{{"Raw file name", type text}, {"End week date", type date}, {"UPC", Int64.Type}, {"Scanned Retail $ - CURRENT", Currency.Type}, {"Scanned Retail $ - PREV", Currency.Type}, {"Gross Margin $ - CURRENT", Currency.Type}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type", "New file name", each let splitEndweekdate = Splitter.SplitTextByDelimiter("-", QuoteStyle.None)(Text.From([End week date], "fr-CA")), splitRawfilename = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Raw file name]) in Text.Combine({"FM_", Text.Combine(splitEndweekdate), Text.Middle(splitRawfilename{0}?, 7, 8)}), type text),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom Column",{"Raw file name", "End week date", "New file name", "UPC", "Description", "Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns2",{"Raw file name", "End week date"}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Removed Columns", "Text After Delimiter", each Text.AfterDelimiter([New file name], "_"), type text),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", "Aide Modulo"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Aide Modulo", type date}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Changed Type1", "Week of Year", each Date.WeekOfYear([Aide Modulo]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Week of Year", "Month", each Date.Month([Aide Modulo]), Int64.Type),
    #"Renamed Columns4" = Table.RenameColumns(#"Inserted Month",{{"Week of Year", "XXXX Year week"}}),
    #"Reordered Columns3" = Table.ReorderColumns(#"Renamed Columns4",{"New file name", "XXXX Year week", "Aide Modulo", "UPC", "Description", "Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT"}),
    #"Reordered Columns4" = Table.ReorderColumns(#"Reordered Columns3",{"New file name", "Aide Modulo", "XXXX Year week", "Month", "UPC", "Description", "Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns4",{{"Aide Modulo", Order.Ascending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Aide Modulo"}, #"FM Fiscal Calendar", {"DateKey"}, "FM Fiscal Calendar", JoinKind.FullOuter),
    #"Expanded FM Fiscal Calendar" = Table.ExpandTableColumn(#"Merged Queries", "FM Fiscal Calendar", {"FY Year", "FY Period", "FY Week", "FY Week Modulo", "FY Period Week Modulo"}, {"FM Fiscal Calendar.FY Year", "FM Fiscal Calendar.FY Period", "FM Fiscal Calendar.FY Week", "FM Fiscal Calendar.FY Week Modulo", "FM Fiscal Calendar.FY Period Week Modulo"}),
    #"Reordered Columns5" = Table.ReorderColumns(#"Expanded FM Fiscal Calendar",{"New file name", "Aide Modulo", "XXXX Year week", "FM Fiscal Calendar.FY Week", "FM Fiscal Calendar.FY Week Modulo", "FM Fiscal Calendar.FY Period Week Modulo", "Month", "FM Fiscal Calendar.FY Year", "FM Fiscal Calendar.FY Period", "UPC", "Description", "Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT"}),
    #"Filtered Rows1" = Table.SelectRows(#"Reordered Columns5", each ([New file name] <> null)),
    #"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows1", "FM Fiscal FY Y+P", each Text.Combine({Text.From([FM Fiscal Calendar.FY Year], "fr-CA"), "-", Text.From([FM Fiscal Calendar.FY Period], "fr-CA")}), type text),
    #"Reordered Columns6" = Table.ReorderColumns(#"Inserted Merged Column",{"New file name", "Aide Modulo", "XXXX Year week", "FM Fiscal Calendar.FY Week", "FM Fiscal Calendar.FY Week Modulo", "FM Fiscal Calendar.FY Period Week Modulo", "Month", "FM Fiscal Calendar.FY Year", "FM Fiscal Calendar.FY Period", "FM Fiscal FY Y+P", "UPC", "Description", "Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT"}),

    // 新增：一次性计算所有周期的完整性校验结果
    #"Period Check Table" = 
        let
            Grouped = Table.Group(#"Reordered Columns6", {"FM Fiscal FY Y+P"}, {
                {"Full Period Check", each if List.Count(List.Distinct([FM Fiscal Calendar.FY Period Week Modulo])) = 4 then "OUI" else null}
            })
        in
            Grouped,

    // 合并校验结果到主表，消除循环引用
    #"Merged Period Check" = Table.NestedJoin(#"Reordered Columns6", {"FM Fiscal FY Y+P"}, #"Period Check Table", {"FM Fiscal FY Y+P"}, "Period Check", JoinKind.LeftOuter),
    #"Expanded Period Check" = Table.ExpandTableColumn(#"Merged Period Check", "Period Check", {"Full Period Check"}, {"Full Period Check"})
in
    #"Expanded Period Check"

关键改动说明 #

• 把原来嵌套在自定义列里的分组校验逻辑抽出来，单独生成Period Check Table，一次性计算所有周期的完整性，避免重复计算
• 通过左连接将校验结果合并到主表，直接匹配每行对应的周期，彻底消除循环引用
• 移除了硬编码的2023-01，自动适配所有财年周期

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