Power BI Service fails to update despite no errors reported in Desktop

[scoutmedic]

I recently created a report in PowerBI Desktop and all works as expected. I published the report to PBI Service and have multiple failed refreshes. The error states:

"Expression.Error: There weren't enough elements in the enumeration to complete the operation.. #table({"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {}). ;There weren't enough elements in the enumeration to complete the operation.. The exception was raised by the IDbCommand interface. Table: Main Hierarchy."

I cleared the cache in Desktop and refreshed each table individually without issues.

None of my tables are displaying any errors. I've checked all data source credentials and no errors are reported. RLS is disabled. (I don't think these are the issues, but worth mentioning.)

Thoughts on how to proceed? 

[scoutmedic]

I confirmed my date slicer is set to "All" and my refresh timezone in Service matches my timezone. 

I deleted the semantic model from Service, cleared my cache, restarted, refreshed locally and no errors occurred. I published it again, verified refresh settings, and still had the same issue. I did confirm my permissions were all set to none in Desktop before publishing. Thought maybe it wouldn't refresh if permissions didn't match, but that wasn't it either.

[lbendlin]

You seem to be trying to ingest files from a folder. Is it on a sharepoint?  Is it maybe a folder on your C: drive?

Please show a sanitized version of the Power Query code.

[scoutmedic]

I recreated the query but reordered the operations and still received the same error for the new query. This table is being used in other reports without issues.

I've isolated it to this table:

let

    Source = SharePoint.Files("httpsxxxxxxxxxxxxx, [ApiVersion = 15]),

    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "FileName.xlsx")),

    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (13)", each #"Transform File (13)"([Content])),

    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (13)"}),

    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (13)", Table.ColumnNames(#"Transform File (13)"(#"Sample File (13)"))),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}, {"F", type text}, {"G", type text}, {"H", type text}, {"I", type text}, {"J", type text}, {"K", type any}, {"L", type text}, {"M", type text}, {"N", type text}, {"O", Int64.Type}, {"P", type text}, {"Column17", type any}}),

    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([A] = "xxx" or [A] = "xxx") and ([B] <> "x" and [B] <> "y" and [B] <> "z")),

    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows2",{"A", "B", "C", "D"}),

    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"A", "W"}, {"B", "X"}, {"C", "Y"}, {"D", "Z"}}),

    #"Removed Duplicates" = Table.Distinct(#"Renamed Columns", {"Z"})

in

    #"Removed Duplicates"

I have not manipulated the queries yet.

[scoutmedic · Accepted solution]

I went through and deleted a few unused helper queries and the issue resolved.

[lbendlin]

That error usually indicates empty tables.  Do you have date filters in your Power Query?  Is your local timezone different from UTC?