I have a report (in import mode) that consumes from some csv files that sit inside of a couple folders in SharePoint Online document libraries. These document libraries receive an additional csv file each day. The report has been refreshing successfully until today. The error message says 'SharePoint: Request failed', with no other helpful information.
I have confirmed that the failure wasn't due to a network drop or a bad file (e.g., all files have the same schema, encoding, etc.). After testing, I found something odd. The report successfully refreshes when there are 133 files in each SharePoint folder, but fails at 134+ files. Since it would be really odd for there to be a file count limit (other than the usual 5k file count limit), I next inspected the individual file size. The files each range from 1kB to 20 kB, for a total file size (in each folder) of only ~1.5 [MB], which is nowhere near the 1-[GB] uncompressed data volume limit. (I'm in shared capacity.)
An on-demand refresh of the report takes only 4 minutes, and a scheduled refresh takes only 21 minutes, which is nowhere near the 2-hour timeout for reports in import mode.
What is causing this problem? As a temporary workaround, I have consolidated some of the files into 1 file. But, eventually, this problem will happen again.
If it helps, below is what my primary query looks like. Note: I am loading all files in said folder via "helper" queries.
let
Source = SharePoint.Files("https://mycompany.sharepoint.com/sites/mysite", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://mycompany.sharepoint.com/sites/mysite/mydoclibrary/myfolder/")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Status", type text}, {"EmployeeID", type text}, {"CreatedDate", type date}})
in
#"Changed Type"
This uses the following "helper" queries:
file:
Sample File (2)
let
Source = SharePoint.Files("https://mycompany.sharepoint.com/sites/mysite", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://mycompany.sharepoint.com/sites/mysite/mydoclibrary/myfolder/")),
Navigation1 = #"Filtered Rows"{0}[Content]
in
Navigation1
Parameter:
Parameter2 = "Sample File (2)"
function:
Transform File (2)
let
Source = (Parameter2 as binary) => let
Source = Csv.Document(Parameter2,[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"
in
Source
