I have a SharePoint Online source and a SQL Server source. The SharePoint source is a list of files containing dates. The SQL Server is a tall table containing dates. My goal is to filter the SQL Server table to all dates that are on or after the minimum date in the SharePoint list of files. I determine my minimum date by first loading all SharePoint files (with helper queries), sorting the dates in ascending order, applying an index column, and then filtering to the lowest index value.
To fetch the minimum date, I am using a cross-apply technique on the SQL Server query. That is, I first create a dummy column whose value is the query name of the minimum date, expand columns on this query to fetch the actual minimum date column, create a custom column with a >= test, and lastly filter to all rows that pass the test.
My problem is that the expand column step is taking a really long time to evaluate, and it's raising "An unexpected error occurred" message in the data preview window (just below the column header). When I click on the supposedly erroneous record (in particular, the value under the "min date" column), it raises the error message:
An error occurred in the 'min date' query. Data Source.Error: SharePoint: Request failed: ...
How can I resolve this?
I have verified that my SharePoint source is configured properly as a data source.
I cannot post a sample pbix file.
