Hi I have a query combining and transforming multiple files from a folder and I have started getting some errors on close and load I get an [Expression.Error] there weren't enough elements in the enumertion. When I open the query to edit the last row is all errors which give same error of

An error occurred in the ‘Transform File’ query. Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[List]

When I click the error I get this = Deduped{104}[FileDateTime] what does the 142 refer to? I can't find it anywhere and I have checked the transform file it all seems correct and give no errors until the final step.

Sorry for such A long post but hope I got all the information needed.

John

Main Query

let
    // Load existing Data table
    ExistingData = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(ExistingData,{{"FileDateTime", type datetime}, {"Date", type date}, {"Week", Int64.Type}, {"QTY", type number}, {"Kg", type number}, {"Use By       Date", type date}}),

    // Force types
    ExistingDataTyped = Table.TransformColumnTypes(#"Changed Type", {{"FileDateTime", type datetime}}),

    // Load NewFiles
    NewFilesTransformed = NewFiles,
    NewFilesTyped = Table.TransformColumnTypes(NewFilesTransformed, {{"FileDateTime", type datetime}}),

    // Add any missing columns from ExistingData to NewFiles (with nulls)
    MissingCols = List.Difference(Table.ColumnNames(ExistingDataTyped), Table.ColumnNames(NewFilesTyped)),
    NewFilesWithCols = List.Accumulate(MissingCols, NewFilesTyped, (state, current) => Table.AddColumn(state, current, each null)),

    // Reorder columns to match ExistingData
    NewFilesOrdered = Table.SelectColumns(NewFilesWithCols, Table.ColumnNames(ExistingDataTyped)),

    // Combine
    Combined = Table.Combine({ExistingDataTyped, NewFilesOrdered}),

    // Remove duplicates
    Deduped = Table.Distinct(Combined),
    // Remove duplicates
    FileDateTime = Deduped{104}[FileDateTime]
in
    FileDateTime

Transform Query

let
    Source = Excel.Workbook(Parameter1, null, true),
    #"Waste Record Sheet1" = Source{[Name="Waste Record Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"Waste Record Sheet1",4),
    #"Kept First Rows" = Table.FirstN(#"Removed Top Rows",1),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] <> null)),
    FirstRowList = #"Filtered Rows"[Column1],
    myDate = try if Value.Is(FirstRowList{1}, type text) then Date.FromText(FirstRowList{1}) else Date.From(FirstRowList{1}) otherwise null,
    MyLoc=FirstRowList{3},
    myWho= FirstRowList{5},
    RemoveTop = Table.Skip(#"Removed Top Rows", 1),
    #"Promoted Headers" = Table.PromoteHeaders(RemoveTop, [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","BBE","",Replacer.ReplaceText,{"Use By       Date"}),
    #"Changed Type" = Table.TransformColumns(#"Replaced Value",{{"Use By       Date", each try if Value.Is(_, type text) then Date.FromText(_) else Date.From(_) otherwise null, type date}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each not ([Reason Code]= null and [PRODUCT CODE] = null and [DESCRIPTION] = null)),
    #"Add Date Col" = Table.AddColumn(#"Filtered Rows1", "Date", each myDate),
    #"Add Area Col" = Table.AddColumn(#"Add Date Col", "Area", each MyLoc),
    #"Add Who Col" = Table.AddColumn(#"Add Area Col", "Completed By:", each myWho),
    #"Changed Type1" = Table.TransformColumnTypes(#"Add Who Col",{{"Date", type date}, {"QTY", type number}, {"Kg", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column10"},MissingField.Ignore),
    AddWeekNumber = Table.AddColumn(#"Removed Columns", "Week", each fxISOYearWeek([Date],null,1)),
    #"Renamed Columns" = Table.RenameColumns(AddWeekNumber,{{"Column9", "Other Reason"}},MissingField.Ignore),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Week", "PRODUCT CODE", "DESCRIPTION", "UOM", "QTY", "Kg", "Use By       Date", "Reason Code", "If Over 10kg approved by", "Other Reason", "Date", "Area", "Completed By:"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Week", Int64.Type}})
in
    #"Changed Type2"