I've been looking at the Advanced Editor for a couple of hours and do not see why this error is occurring. Below is the code from the Advanced Editor. Any help with a fresh pair of eyes would be appreciated!
let
Source = SharePoint.Files("https://dcocf.sharepoint.com/sites/dataproject", [ApiVersion = 15]),
#"Extracted text after delimiter" = Table.TransformColumns(Source, {{"Folder Path", each Text.AfterDelimiter(_, "https://dcocf.sharepoint.com/sites/DataProject/", 0), type text}}),
#"Split column by delimiter" = Table.SplitColumn(#"Extracted text after delimiter", "Folder Path", Splitter.SplitTextByDelimiter("/"), {"Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4", "Folder Path.5", "Folder Path.6", "Folder Path.7"}),
#"Changed column type" = Table.TransformColumnTypes(#"Split column by delimiter", {{"Folder Path.1", type text}, {"Folder Path.2", type text}, {"Folder Path.3", type text}, {"Folder Path.4", type text}, {"Folder Path.5", type text}, {"Folder Path.6", type text}, {"Folder Path.7", type text}}),
#"Filtered rows" = Table.SelectRows(#"Changed column type", each [Folder Path.2] = "WIP" and [Folder Path.3] = "WIP Reports" and [Folder Path.4] = "WIP TEST" and [Name] <> "WIP_Month_End_November_2023_Aggregated_113023.xlsm" and [Name] <> "WIP_SNAPSHOT_REPORT 2024-01-13 07 00.xlsx"),
Source2 = SharePoint.Files("https://dcocf.sharepoint.com/sites/Reporting", [ApiVersion = 15]),
Custom = Table.TransformColumns(Source2, {{"Folder Path", each Text.AfterDelimiter(_, "https://dcocf.sharepoint.com/sites/Reporting/", 0), type text}}),
#"Custom 1" = Table.SplitColumn(Custom, "Folder Path", Splitter.SplitTextByDelimiter("/"), {"Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4", "Folder Path.5", "Folder Path.6", "Folder Path.7"}),
#"Filtered rows 3" = Table.SelectRows(#"Custom 1", each [Folder Path.1] = "ReleasedNotForConsumption" and [Name] = GetWIP),
#"Filtered rows 2" = Table.SelectRows(#"Filtered rows 3", each ([Folder Path.2] = "")),
#"Filtered rows 1" = Table.SelectRows(#"Filtered rows 2", each ([Folder Path.2] = "")),
#"Appended query" = Table.Combine({#"Filtered rows 1", #"Filtered rows"}),
#"Filtered rows 4" = Table.SelectRows(#"Appended query", each ([Name] = "WIP_SNAPSHOT_REPORT 2024-03-01 07 14.xlsx")),
#"Invoke custom function" = Table.AddColumn(#"Filtered rows 4", "Transform file", each #"Transform file"([Content])),
#"Removed other columns" = Table.SelectColumns(#"Invoke custom function", {"Transform file"}),
#"Expanded Transform file" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", {"ORDER GROUP NUMBER", "PROJECT EXECUTIVE", "CLIENT NAME", "PROJECT NAME", "PROJECTED CONTRACT TOTAL", "BILLED", "BALANCE TO FINISH", "REPORT DATE", "Project ID"}, {"ORDER GROUP NUMBER", "PROJECT EXECUTIVE", "CLIENT NAME", "PROJECT NAME", "PROJECTED CONTRACT TOTAL", "BILLED", "BALANCE TO FINISH", "REPORT DATE", "Project ID"}),
#"Changed column type 1" = Table.TransformColumnTypes(#"Expanded Transform file", {{"REPORT DATE", type date}, {"BALANCE TO FINISH", type number}, {"BILLED", type number}, {"PROJECT NAME", type text}, {"CLIENT NAME", type text}, {"PROJECT EXECUTIVE", type text}, {"ORDER GROUP NUMBER", type text}, {"PROJECTED CONTRACT TOTAL", type number}, {"Project ID", type text}}),
#"Merged queries 1" = Table.NestedJoin(#"Changed column type 1", {"Project ID"}, PrimaryLabels, {"Project ID"}, "PrimaryLabels", JoinKind.LeftOuter),
#"Expanded PrimaryLabels" = Table.ExpandTableColumn(#"Merged queries 1", "PrimaryLabels", {"Client Name", "Project Name", "Salesperson1"}, {"Client Name.1", "Project Name.1", "Salesperson"}),
#"Replaced value 1" = Table.ReplaceValue(#"Expanded PrimaryLabels", each [CLIENT NAME], each if [Client Name.1]<> null then [Client Name.1] else [CLIENT NAME], Replacer.ReplaceValue, {"CLIENT NAME"}),
#"Changed column type 3" = Table.TransformColumnTypes(#"Replaced value 1", {{"Project ID", type text}}),
#"Replaced value 3" = Table.ReplaceValue(#"Changed column type 3", each [PROJECT NAME], each if [Project ID] = [ORDER GROUP NUMBER] then [Project Name.1] else [PROJECT NAME], Replacer.ReplaceValue, {"PROJECT NAME"}),
#"Replaced value 4" = Table.ReplaceValue(#"Replaced value 3", each [PROJECT EXECUTIVE], each if [Salesperson]<>null then [Salesperson] else [PROJECT EXECUTIVE], Replacer.ReplaceValue, {"PROJECT EXECUTIVE"}),
#"Merged queries 4" = Table.NestedJoin(#"Replaced value 4", {"Project ID"}, Portfolio, {"Project ID"}, "Portfolio", JoinKind.LeftOuter),
#"Expanded Portfolio" = Table.ExpandTableColumn(#"Merged queries 4", "Portfolio", {"Project Name"}, {"Project Name.2"}),
#"Replaced value 5" = Table.ReplaceValue(#"Expanded Portfolio", each [PROJECT NAME], each if [Project Name.2]<>null then [Project Name.2] else [PROJECT NAME], Replacer.ReplaceValue, {"PROJECT NAME"}),
#"Uppercased text" = Table.TransformColumns(#"Replaced value 5", {{"PROJECT NAME", each Text.Upper(Text.From(_)), type nullable text}}),
#"Removed columns" = Table.RemoveColumns(#"Uppercased text", {"Client Name.1", "Project Name.1", "Salesperson", "Project Name.2"}),
#"Merged queries 3" = Table.NestedJoin(#"Removed columns", {"Project ID"}, PrimaryCloseDates, {"Project ID"}, "PrimaryCloseDates", JoinKind.LeftOuter),
#"Expanded PrimaryCloseDates" = Table.ExpandTableColumn(#"Merged queries 3", "PrimaryCloseDates", {"Order Delivery Date"}, {"PRIMARY CLOSE DATE"}),
#"Transform columns" = Table.TransformColumnTypes(#"Expanded PrimaryCloseDates", {{"PROJECT EXECUTIVE", type text}, {"CLIENT NAME", type text}, {"PROJECT NAME", type text}}),
#"Transform columns 1" = Table.TransformColumnTypes(#"Transform columns", {{"PRIMARY CLOSE DATE", type text}}),
#"Changed column type 2" = Table.TransformColumnTypes(#"Transform columns 1", {{"PRIMARY CLOSE DATE", type date}}),
#"Sorted rows" = Table.Sort(#"Changed column type 2", {{"REPORT DATE", Order.Ascending}})
in
#"Sorted rows"
