•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
I have a power query in sharepoint that consolidates all a folder with many other excel files. How do i make it such that it refreshes automatically without opening it?
Our take
Managing large datasets in SharePoint can be challenging, especially when manual refreshes consume valuable time. If you're working with a Power Query that consolidates numerous Excel files, automating the refresh process can significantly enhance productivity for you and your team. By setting up an automatic refresh, you can ensure your dataset is updated first thing in the morning, saving you the 5 to 10 minutes spent on manual updates. Let’s explore how to implement this automation effectively for optimal efficiency.
hi all, as my title states. the total rows adds up to around 100k , up to 500k by the end of the year. it takea me roughly 5 to 10 mins for the power query to fresh the entire dataset manually each time.
This consolidated file is also used my many others.
Hence i was wondering if i can have it auto refreshed lets say first thing in the morning.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to refresh a table that is tied to a sharepoint form without going to the table every timeHello! I have a couple of tables that are populated from forms that various team members fill out and I bring this into a sales report via Power Query. For quite a long time, when I would refresh the PQ query that would bring in the new data, the table would auto refresh but awhile ago there was a change that broke this automatic refresh (I think a MS update from awhile ago?) and now I have to go to each table that has a form, open the sharepoint link, let it update, and then refresh my query to bring in the data. This is an improvement from a share file that had too many issues with users entering wrong data and has been the best solution for a while but the having to manually go to each table and refresh it daily is driving me crazy. What are my options to get this to refresh automatically if its within a form? I tried to dip my toe into power automate but for some reason these forms/tables do not show up within it and I have not had success using that (but I could also be doing it wrong). submitted by /u/Moudy90 [link] [comments]
- I need a way to combine multiple workbooks to use in a pivot table that can be refreshed through sharepoint.I have 5 spreadsheets in a folder on sharepoint and one on a separate folder in the same team site. These track contacts made by clients. Each workbook is for a LGA (location) and have the same structure. they have a sheet for each month and a table for that month. I initially struggled to use the Sharepoint Folder to combine these so created queries in Power Query. for each workbook I created a query which combined the 12 tables and then created a query which appended them. I was able to put this in a workbook and a pivot table to summarise where we were up to. it worked really well. the permissions were set using organisational account. this lasted about a day and now I cannot refresh it. I can refresh in the power query editor but not in the spreadsheet. I have opened it in the desktop version and also the same problem. I am now getting errors about legacy connections. I then tried the Sharepoint folder which finally worked but seem I can combine the January tables for the five spreadsheets but then I would need to do the same for each month. I would love any suggestions for this to consistently work and refresh from our sharepoint site (excel online). submitted by /u/Eastern_Echidna5680 [link] [comments]
- Power Query refresh speed with multiple usersHello all. I have a report that has multiple queries with data sources saved in SharePoint. Enable Fast Data load is checked in all the queries. There are about 10 users, with reported run time of around 5-10 minutes. But for me and one user, it takes 30-60 minutes to finish refreshing. I have already made some improvements in the initial code, and did a clean up of old raw data files in SharePoint but this resulted to minimal improvements. Given that all users have the same laptop configurations and are connected to the same internet connection, what are some steps I can do to do improve the speed on the queries? submitted by /u/SYSTEMOFADAMN [link] [comments]
- Excel Power Query refresh suddenly incredibly slowHi everyone, I have a file that I refresh daily with several queries. One of those became incredibly slow (few seconds to hasn't finished yet) from one day to the next. Nothing changed in the file or source, it is not very large (~5000 lines) and without any manipulations other than changing the data type. I have tried to change the privacy levels, background refresh, fast load and so on as I found online, but nothing helped. How can I solve this? Thank you! submitted by /u/Loose_Biscotti9075 [link] [comments]
Tagged with
#rows.com#Excel alternatives for data analysis#Excel compatibility#Excel alternatives#natural language processing for spreadsheets#generative AI for data analysis#large dataset processing#real-time data collaboration#real-time collaboration#financial modeling with spreadsheets#power query#sharepoint#excel files#refreshes automatically#auto refreshed#consolidates#consolidated file#dataset#automation#rows