cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Long Refresh Time for Sharepoint Folder

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Long Refresh Time for Sharepoint Folder

@KCinMelbourne,

 

The following article lists some useful tips.

https://www.thebiccountant.com/speedperformance-aspects/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Long Refresh Time for Sharepoint Folder

@KCinMelbourne,

 

The following article lists some useful tips.

https://www.thebiccountant.com/speedperformance-aspects/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Long Refresh Time for Sharepoint Folder

Thanks @v-chuncz-msft

I will work through each of the suggestions in the blog.  Appreciate your advice.

KC