Refresh error: There weren't enough elements in the enumeration to complete the operation

[SimoneFick]

Hi, I please need help with the following Query issue:

I have to run a refresh on data weekly. The queries were written by someone else and handed over to me. 

The queries pull in data for This year and Last year values from .csv files all located in a company server folder.

There are 2 queries, 1 for this year and 1 for last year which are in different pivot tables (sliders connected to select a week's data for this year and another one for a week's data last year.

When the previous guy press refresh the queries run without problems (it takes very long because of file sizes) but when I press refresh I get an error message "We couldn't refresh the connection 'Query - Last year'. [Expression.error] There weren't enough elements in the enumeration to complete the operation".

I'll the source links are correct, I've checked.

When I edit the 'Query -Last year' the issue shows up in 1 step

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

So I narrowed it down to an issue in the Sample file query.

When I edit the Sample File query I get an empty table on the 2nd step (filter rows) after the source loads:

= Table.SelectRows(Source, each ([Date created] = #datetime(2020, 9, 14, 6, 47, 28.1097561) or [Date created] = #datetime(2020, 9, 14, 7, 8, 48.9290775) or [Date created] = #datetime(2020, 9, 14, 10, 27, 29.7088766) or [Date created] = #datetime(2020, 9, 14, 10, 37, 26.9553959) or [Date created] = #datetime(2020, 9, 14, 10, 47, 36.2503587) or [Date created] = #datetime(2020, 9, 14, 12, 32, 39.7303296) or [Date created] = #datetime(2020, 9, 14, 19, 27, 30.1749386) or [Date created] = #datetime(2020, 9, 14, 19, 47, 28.4966736) or [Date created] = #datetime(2020, 9, 15, 6, 52, 26.3579856) or [Date created] = #datetime(2020, 9, 15, 7, 23, 48.1798885) or [Date created] = #datetime(2020, 9, 15, 20, 52, 25.451252) or [Date created] = #datetime(2020, 9, 16, 0, 12, 29.9211136) or [Date created] = #datetime(2020, 9, 16, 1, 22, 30.2543473) or [Date created] = #datetime(2020, 9, 16, 6, 55, 4.4521125) or [Date created] = #datetime(2020, 9, 16, 7, 28, 42.811592) or [Date created] = #datetime(2020, 9, 16, 9, 2, 30.3399247) or [Date created] = #datetime(2020, 9, 17, 5, 42, 35.0756519) or [Date created] = #datetime(2020, 9, 17, 7, 28, 36.0902063) or [Date created] = #datetime(2020, 9, 18, 7, 23, 34.89573) or [Date created] = #datetime(2020, 9, 18, 9, 54, 32.7346747) or [Date created] = #datetime(2020, 9, 19, 6, 49, 4.5187354) or [Date created] = #datetime(2020, 9, 19, 7, 41, 25.0241552) or [Date created] = #datetime(2020, 9, 19, 16, 30, 16.2043991) or [Date created] = #datetime(2020, 9, 20, 7, 8, 55.3716273) or [Date created] = #datetime(2020, 9, 20, 7, 9, 43.998896) or [Date created] = #datetime(2020, 9, 20, 21, 42, 34.9859283)))

I know this would've been created when there was less data in the folder(Data added daily to the folder) and should just be an example for the source. But I get the same error every time I press refresh, where the previous guy tested it again on his side and it works without this error coming up.

I'm not advanced in Power query but know my way around. Any help will be greatly appreciated.

[SimoneFick · Accepted solution]

I found the problem last night...

The previous guy is in a different country, and we're both using the same server. When the files get loaded into the folder the timestamps are different for both of us(different time zones). In his query the [Date created] had hardcoded times in, which was obivously different to the times I see. 

I changed the SelectRows to be between dates (I need a week's worth and since this is just the sample file) and I changed the time stamp to be midnight for both dates. It worked!! 

There is probably a better solution then adding times (best if it can be avoided at all in my case) but it is working without problems now.