Power BI Refresh Error In Workspace

[KJChin]

I have applied incremental refresh to two tables in Power BI and published to workspace. When refreshed, it showed error as this:

Data source errorExpression.Error: There weren't enough elements in the enumeration to complete the operation.. #table({"MonthYear", "Latest Entry", "Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Text Between Delimiters", "MonthYear.1", "Keep file"}, {}). Microsoft.Data.Mashup.ErrorCode = 10063. Detail = #table({"MonthYear", "Latest Entry", "Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Text Between Delimiters", "MonthYear.1", "Keep file"}, {}). . The exception was raised by the IDbCommand interface.

This report was initially published without incremental refresh. And it has been run normally without fail daily for one year. However, after applying the method, the error keeps appearing. 

The data source is Excel files stored in SharePoint Folder, both datasets start from January 2022. I set RangeStart/RangeEnd from 01/01/2022 to 12/31/2050. Other settings are shown in screenshot below.
 

I tried to debug, by checking the RangeStart/RangeEnd, the number of columns, the column header naming, the table structure etc. I suspect (although it seems not the case) it is due to inconsistent column number or the naming, which I had solved it previously by forcing them. For column number, I forced to take first 37 columns using FirstN, while the header name I rename using List, all are done in Transform File. Here is part of the Power Query code in MAIN query.

let
    Source = SharePoint.Files("https://xxx.sharepoint.com/sites/ABCD", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "ABCD ")),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Filtered Rows", "Text Between Delimiters", each Text.BetweenDelimiters([Name], "ABCD for ", " (To Mgmt).xlsx"), type text),
    #"Trimmed Text" = Table.TransformColumns(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "MonthYear", each 
        let
            // Remove prefix like 'mid' if it exists
            CleanedText = 
                if Text.Contains([Text Between Delimiters], "mid ") then
                    Text.RemoveRange([Text Between Delimiters], 0, Text.PositionOf([Text Between Delimiters], "mid ") + 4)
                else
                    [Text Between Delimiters],
            
            // Extract the cleaned parts
            LengthCheck = Text.Length(Text.AfterDelimiter(CleanedText, " ")) = 2,
            FinalText = 
                if LengthCheck then
                    Text.BeforeDelimiter(CleanedText, " ") & " 20" & Text.AfterDelimiter(CleanedText, " ")
                else
                    CleanedText
        in
            FinalText
),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"MonthYear"}, {{"Latest Entry", each List.Max([Date created]), type datetime}, {"All", each _, type table [Content=binary, Name=text, Extension=text, Date accessed=datetime, Date modified=datetime, Date created=datetime, Attributes=record, Folder Path=text, Text Between Delimiters=text, MonthYear=text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Text Between Delimiters", "MonthYear"}, {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Text Between Delimiters", "MonthYear.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded All", "Keep file", each if [Latest Entry] = [Date created] then "Keep" 
    else "Ignore"),
    #"Filtered Rows3" = Table.SelectRows(#"Added Custom1", each ([Keep file] = "Keep")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows3",{{"MonthYear", type datetime}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Date created] >= RangeStart and [Date created] < RangeEnd),
    #"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function2" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File (7)", each #"Transform File (7)"([Content])),
    #"Removed Other Columns2" = Table.SelectColumns(#"Invoke Custom Function2", {"Date created","MonthYear","Transform File (7)"}),
    #"Expanded Table Column2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Transform File (7)", Table.ColumnNames(#"Transform File (7)"(#"Sample File (7)"))),

Does anyone face the same issue and how you tackle it? Any idea is much appreciated. Thank you.

[GilbertQ]

Hi @KJChin 

What I typically recommend for people to do is to make sure as soon as you can in your steps you transform the column which are going to use for incremental refresh to data and then apply the incremental refresh range start and range end. There and then meaning it is applied within the first two steps. That should typically resolve any other issues that you have.

[Gabry · Accepted solution]

Hey buddy,

try to change the last step, set fixed columns names

#"Expanded Table Column2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Transform File (7)", {"Column1", "Column2", "Column3", /* ... list all columns names here ... */})

[KJChin]

It is working! I have been stuck here for a week. You saved my day. Thank you very much!