Expression.Error: A cyclic reference was encountered during evaluation

Question

I begin with Power Query and i need help.

Context: i merge different Sales reports, add a date table based on Customer Fiscal Year. Sales informations are monthly and i need to transform them on weekly base. A period is composed of 4 weeks and we received each week a report where sales are added. So, i need to check if the 4 reports of the period are loaded to be able to deduce Week 1+2+3 from full Period, then Sales of week 1+2 from sales Week 1+2+3, and Sales of week 1 from sales week 1+2.... Final is to get sales of each week

Sorry, i get a headtache :-P

The error message in title appears after i put this part of code:

let
    Source = #"FM Cleaned data PTD",
    #"Filtered Rows" = Table.SelectRows(Source, each [#"FM Fiscal FY Y+P"] = XYXY ),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"FM Fiscal FY Y+P"}, {{"Count", each List.Count(List.Distinct(List.Transform([FM Fiscal Calendar.FY Period Week Modulo], each Number.Mod(_, 4))))}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Full Period Check", each if [Count] = 4 then "OUI" else null)
in
    #"Added Custom"

I understand there is somewhere an infinite table cycle but i am not able to find it. The whole code is:

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"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns6", "Full Period Check", each 
    let
        Source = #"FM Cleaned data PTD",
        #"Filtered Rows" = Table.SelectRows(Source, each [#"FM Fiscal FY Y+P"] = 2023-01),
        #"Grouped Rows" = Table.Group(#"Filtered Rows", {"FM Fiscal FY Y+P"}, {{"Count", each List.Count(List.Distinct(List.Transform([FM Fiscal Calendar.FY Period Week Modulo], each Number.Mod(_, 4))))}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Full Period Check", each if [Count] = 4 then "OUI" else null)
    in
        #"Added Custom")
in
    #"Added Custom"

Thanks a lot!

Answer 1

Try renaming the sub let ... in ... steps so that they are unique across the whole script. For example:

    #"Added Custom" = Table.AddColumn(#"Reordered Columns6", "Full Period Check", each 
    let
        #"Sub Source" = #"Reordered Columns6",
        #"Sub Filtered Rows" = Table.SelectRows(#"Sub Source", each [#"FM Fiscal FY Y+P"] = 2023-01),
        #"Sub Grouped Rows" = Table.Group(#"Sub Filtered Rows", {"FM Fiscal FY Y+P"}, {{"Count", each List.Count(List.Distinct(List.Transform([FM Fiscal Calendar.FY Period Week Modulo], each Number.Mod(_, 4))))}}),
        #"Sub Added Custom" = Table.AddColumn(#"Sub Grouped Rows", "Full Period Check", each if [Count] = 4 then "OUI" else null)
    in
        #"Sub Added Custom")
in
    #"Added Custom"

Note how #"Sub Source" has been updated to reference the previous step. You are getting the cyclic reference because you had it reference the query itself that it is in.