Scheduled refresh from Exchange fails:"Not enough elements in the enumeration to complete operation"

[mscno]

Hi everyone,

This is my first post on the forums.

I have created a query that sources Exchange data. More precisely the query goes into Exchange finds a particular email and opens the attachment as an excel workbook.

The query works fine in Power BI Desktop, but the scheduled refresh fails online with the message: "There weren't enough elements in the enumeration to complete the operation".

The first part of my query enters into my mailbox and finds a particular email and opens the attachment:

Summary of the Query Code:

Name: File_Fetch (2)

let
Source = Exchange.Contents("[EDITED]"),
Mail1 = Source{[Name="Mail"]}[Data],
#"Filtered Rows" = Table.SelectRows(Mail1, each ([Folder Path] = "[ACTUAL PATH]")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"DateTimeReceived", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Attachments"}),
#"Expanded Attachments" = Table.ExpandTableColumn(#"Removed Other Columns", "Attachments", {"AttachmentContent"}, {"Attachments.AttachmentContent"}),
Navigation1 = #"Expanded Attachments"{0}[Attachments.AttachmentContent]
in
Navigation1

This file attachment is then saved as a binary parameter :

Name: File_Name (2)

#"File_Fetch (2)" meta [IsParameterQuery=true, BinaryIdentifier=#"File_Fetch (2)", Type="Binary", IsParameterQueryRequired=true]

This parameter is then used as a soure to open the actual tables located inside the excel:

let
Source = Excel.Workbook(#"File_Name (2)", null, true),
API = Source{[Name="API"]}[Data],
#"Changed Type1" = Table.TransformColumnTypes(API,{{"Column1", type number}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Column1", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "API"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "API"})
in
#"Reordered Columns"

The query works perfectly on my when querying it in Power BI Desktop, but the refresh does not work online. I have entered the credentials correct online, so this is not the issue.

Anyone who have experienced similar issues, or have an idea what causes the scheduled refresh to fail?

[v-yuezhe-msft]

@mscno ,

The above error message mentions API2 table,  I haven’t seen the query code about API2, are you able to refresh the API2 table in Power BI Desktop?

Regards,
Lydia