OLE DB or ODBC error: [Expression.Error] There weren't enough elements in the enumeration to complet

[H_insight]

Hi,

I am combining multiple files (100's) from SharePoint and I am trying to dynamically promote the correct header per file at once.

Here is a copy of the m code:

let
    Source = Excel.Workbook(Parameter1, null, true),
    #"ExcelFiles_Sheet" = Source{[Item="ExcelFiles",Kind="Sheet"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(#"ExcelFiles_Sheet",{"Column2", "Column3"}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Other Columns", {"Column3"}),
    #"Added" = Table.AddColumn(#"Removed Errors", "Client", each if [Column2] = "Client" then [Column3] else null),
    #"Added 1" = Table.AddColumn(#"Added Client Name", "Department", each if [Column2] = "Department" then [Column3] else null),
    #"Filled Down" = Table.FillDown(#"Added Project Name/Relase",{"Client", "Department"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Client", "Department"}),#"Added Index" = Table.AddIndexColumn(#"Filled Up", "Index", 0, 1, Int64.Type),
    #"Filtered Rows1" = Table.SelectRows(#"Added Index", each [Column3] <> "#REF!"),    
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1","#REF!"," ",Replacer.ReplaceValue,{"Column3"}),
    #"Identify Header Number" = Table.SelectRows(#"Replaced Value", each [Column2] = "Goal")[Index]{0},
    #"Dynamic Header= Goal" = Table.Skip(#"Replaced Value", #"Identify Header Number"),
    #"Filtered Rows" = Table.SelectRows(#"Dynamic Header= Goal", each ([Column3] <> null) and ([Column2] <> "Goal")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column2", "Goal"}, {"Column3", "Mandays"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Budget", each "Approved Budget"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Goal", type text}, {"Mandays", Int64.Type}, {"Client", type text}, {"Department", type text}, {"Budget", type text}})
in
    #"Changed Type"

I think my issue is when I try to use the index to identify the row number and then use it to promote the header. But I am not sure which file is causing the issue?

I have tried removing errors and replacing values, ..with no success.

Any idea is greatly appreciated.

Screenshot of the applied steps in "Transform Sample File":

Many thanks

[Anonymous]

Hi @H_insight ,

Refer to:

Solved: Expression.Error: There weren't enough elements in... - Microsoft Power BI Community

How to fix the "Too many elements in the enumeration to complete..." pivot error in Power ...

Best Regards,
Eyelyn Qin