Forum Discussion
Adding New Queries Takes Forever
Hi ddbaker - no, you can do the transformations in Power Query.
- Do the combine operation as normal.
- You will have a large listing of data. That needs to be cleaned up.
- Go to the "Sample Query" that Power Query created as part of the combine operation. From there, do the individual unpivots, renaming, etc. So you are transforming one file there.
- Power Query will then use that logic and apply to every file and drop it in your final combined query.
It sounds like ddbaker you are adding a new query for each file. This is very inefficient. You should instead be using the Combine Files function which will let Power Query connect to 1 or 1,000 files in a single folder (or range of folders if in SharePoint) that mean as new files are added, Power Query automatically picks them up. There are more details on this process here.
Ideally, you'd stick these files in a SharePoint site in one folder, then user SharePoint.Contents to consume them. You can use SharePoint.Files (the default SharePoint folder connection) but it scans the entire SP site. SharePoint.Contents only looks at one folder, and it is my go-to connection for SharePoint files.
You can also use Azure Data Lake for Excel and CSV files, which has even better performance, but that would require a bit of assitance form someone in your IT department to get a subscription, set up a Datalake site, and a process to upload and process files.
Thank you for your post edhans. I have the same issue where it takes forever to process any change. Will most surely investigate what you propose.
On the othe hand - ddbaker , my laptop was also running out of RAM because I only had 8Gb of RAM. I upgraded the RAM to 16GB and my computer RAM runs at 50-60% most of the time - problem solved, but it is still taking forever to process changes, hence why I will investigate what ddbaker suggests.