Hi all,
I'm trying to improve the performance of a PowerBI Model we use for consolidation. We receive between 50 - 100 excel forecast files (.xlsm) from members of the business that we save onto Sharepoint. We then extract around 6 tables from each file that we consolidate in PowerBI. It takes somewhere between 10 minutes and 45 minutes depending on my connection and the number of files.
I simplified the model down to retrieve just one of the tables from the full data set with no manipulation and it takes about 5 minutes 30 seconds. So - Question 1 Does anyone have any suggestions for whether its possible to improve the time taken to retrieve data or is this as expected?
let
Source = SharePoint.Files(P_SharePointLocation, [ApiVersion = 15]),
#"Filtered on Source File" = Table.SelectRows(Source, each Text.StartsWith([Folder Path], P_SharePointProjFcstSubmissions) or Text.StartsWith([Folder Path], P_SharePointProjBudSubmissions)),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered on Source File", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Source_PrjFcst_NonITResource", each #"Transform File from Source_PrjFcst_NonITResource"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Source_PrjFcst_NonITResource"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Source_PrjFcst_NonITResource", Table.ColumnNames(#"Transform File from Source_PrjFcst_NonITResource"(#"Sample File")))
in
#"Expanded Table Column1"
Secondary to this, when I build a second retrieve to pull another table from the same files, the time taken to refresh is almost identical. This indicates the time doesn't double by pulling double the number of tables.
This leads me to question 2 - I believe that a large portion of my extended refresh is due to the manipulation of the data after import rather than the import itself. Troubleshooting tips for finding the 'slow parts' of my queries would also be much appreciated.
Thanks
KC
