There weren't enough elements in the enumeration to complete the operation. when threre are no files

[idgalvan]

Hello,

I'm using the get from folder to get a few result files from a process I run daily. the results contain multiple columns and one of them is an XML that I also parse to get individual values into the report.

Recently on Sundays and holidays, the process returns 0 results aka 0 files and PowerBi can't refresh due to getting an error when trying to refresh this folder. 

There weren't enough elements in the enumeration to complete the operation.

This is a let steps in result query, but i'm wondering if there is a way to try or add checks so this does not return an error and allows for the report to refresh successfully?

[Anonymous]

Hi  @idgalvan,

Based on my understanding, you want to add error handling formula into your query,right?

If this is a case, you can add below bold part formula to your query:

let
    Source = Xml.Tables(File.Contents("C:\Users\xxxxx\Desktop\abc.xml")),
    Table0 = try Source{0}[Table] otherwise null
in
    Table0

It will return null if source get data failed.

 failedsuccessed

BTW, you can also use #table() function to create a empty table to replace the error result.

Reference link:

Creating Tables In Power BI/Power Query M Code Using #table()

Regards,

Xiaoxin Sheng

[idgalvan]

Hi,

Not exactly what im doing/failing. Here is my query(minus some redacted fields for protection):

let
    Source = Folder.Files("<foldername>"),
    #"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File", each #"Transform File from Source"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Source"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Source", Table.ColumnNames(#"Transform File from Source"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(),
    #"Inserted Text Between Delimiters" = Table.AddColumn(),
    #"Renamed Columns" = Table.RenameColumns(),
    #"Filtered Rows" = Table.SelectRows()
in
    #"Filtered Rows"

This fails to refresh if there are no folders with"There weren't enough elements in the enumeration to complete the operation."

I try to create a function encircling thisi with

let

Function =() =>

//abovecode

in 

Fuction

and then doing a query:

source = try Function() otherwise null but this fail with stack overflow. any ideas are appreciated. 

[MarcelBeug]

If the situation is that the table at Source is empty then you can add a step at the end of your original query:

    Result = if Table.IsEmpty(Source) then #table(0,{}) else #"Filtered Rows"
in
    Result

Due to lazy evaluation, all other code in the query will not be evaluated if Source is empty,

Instead of #table(0,{}) you may want to create an empty, dummy table in another query and refer to this table.