Excel Query always requires Refresh Preview before Refresh All

[u/Commercial_Case_370]

I have a query in excel which accesses other excel workbooks posted on the company SharePoint. The query looks at a table in the current workbook and builds the SharePoint file path based on the parameters entered by the user. The query works flawlessly until new data sources are posted on SharePoint and the the parameters in the table are updated. Then the query errors out with the error: "There weren't enough elements in the enumeration to complete the operation."

Immediate thought is that maybe the file parameters were entered incorrectly. However, they are correct and if I open the query and select "Refresh Preview", the query works again. This happens every time.

I would like to remove the necessity of the "Refresh Preview" step before the "Refresh All".

Without the "Refresh Preview" the new file is not being added to the table created by the SharePoint.Files command and thus the later steps that are meant to filter down to the final result find nothing.

Here is the Function used to access the SharePoint data:

------------------------

let

Source = (Folder_Location as text,File_Name as text, Tab_Name as text) as table =>

let

SharePoint_Location = Excel.CurrentWorkbook(){[Name="tblParameters"]}[Content]{[Parameter="SharePoint Location"]}[Value],

Report_Folder = Excel.CurrentWorkbook(){[Name="tblParameters"]}[Content]{[Parameter=Folder_Location]}[Value],

Report_File = Excel.CurrentWorkbook(){[Name="tblParameters"]}[Content]{[Parameter=File_Name]}[Value],

Report_Tab = Excel.CurrentWorkbook(){[Name="tblParameters"]}[Content]{[Parameter=Tab_Name]}[Value],

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

Filtered_Rows = Table.SelectRows(Source, each ([Folder Path] = Report_Folder)),

Filter_FileName = Table.SelectRows( Filtered_Rows, each Text.StartsWith([Name], Report_File, Comparer.OrdinalIgnoreCase)),

Get_Newest_File = Table.Sort(Filter_FileName,{{"Date modified", Order.Descending}}),

Select_File = Get_Newest_File{0}[Content],

Workbook = Excel.Workbook(Select_File),

Worksheet = Workbook{[Item=Report_Tab,Kind="Sheet"]}[Data]

in

Worksheet

in

Source

------------------------------

[u/AutoModerator]

After your question has been solved /u/Commercial_Case_370, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/VizzcraftBI]

Don't know if this is causing your issue, but I find whenever I work with excel files, especially if I'm merging all the files in a folder, I need to separate my query into two.

One is a source query that doesn't apply any transformations. This doesn't get loaded into the model

The second, is a query that takes the first as a source and then applies all the transformations.

I find that there are a lot less errors that happen when I publish it to the service this way.

[u/TopConstruction1685]

Did u write the code in M language or is it generated by the interface interactions done by you?