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.
Hi edhans, thank you so much for the response. I apologize for the delay in getting back to you. I didn't know about the Combine feature in Power Query, so thank you for telling me about it. I've been trying it out this morning and doing some research, and I'm running into a couple of issues. First, the files I download are CSV exports of our Profit and Loss and Balance Sheets from QuickBooks. Each export goes through the exact same steps to transform it into "flat" data (i.e. Unpivoting, renaming columns, etc.) that can be used for analysis in PBI. Hence my first approach of transforming each individual export (by copying and pasting the code and changing the source) and then appending into a single query. If I were to use the "Combine" feature, would I have to transform each individual file (in Excel Power Query or something similar) and then upload each transformed file into a folder that can be used to Combine all the data? I'm willing to take the time to do this, but I want to make sure there isn't a better way before I spend the time doing it. I appreciate your help!
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.
edhans Thank you for all your help. I got all that to work, now my only struggle is connecting to SharePoint. I've read several articles and watched a couple YouTube videos, and I keep getting the error: DataSource.Error: SharePoint: Request failed: The remote server returned an error: (400) Bad Request. (Bad Request). I've never had this error whenever I connect to single files via SharePoint. Any idea what I'm doing wrong? Do you perhaps have a good article you can link to that will explain how to do combines with SharePoint?
This article ddbaker will walk you through the process. It is very simple, so if you are getting a bad request, either you are combining files that cannot be combined (word files for example), or your SharePoint admin has some security setting set that you need to discuss with them and get it fixed. It is not anything you are doing wrong in Power Query itself as long as it is Excel, PDF, JSON, XML, or CSV files.