Forum Discussion
Slow query - multiple appended queries
Hi Sammycat ,
Put all of your text files into a single folder, either on your network or SharePoint.
In Power Query, use a Folder Connector to connect to this folder.
When prompted, choose 'Transform & Combine" (or similar wording).
This will generate you a load of helper queries. The one you want is called something like 'Transform Sample File'. Whatever transformations you apply to one file in this query will be applied to all files in the same folder, and all files transformed in this way will be automatically appended into a master query.
Once you recive your new file each month, drop it into the same folder with the rest and, the next time your report refreshs, it will include the new data in the process. Voila!
More details:
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-combine-binaries
Pete
Hi BA_Pete
The file is created automatically from a process control system and is updated hourly. There are several files created and dumped periodically with different schemas and data - I'm only interested in one. So for this to work would require manually copying the required file to a dedicated folder each time the query was to be refreshed.
Where does the process control system save the generated files to?
You could use Power Auomate to detect when a new file with a specific name was generated and have it automatically copy the file into your query source folder.
Pete
The files are saved to an on premises server.
Ok, is that some form of Blob Storage? If so, you could potentially query the file structure with PQ, filter to only files that have a name like the ones you want, and replicate the Folder Connector behaviour by creating your transformations in a function.
It sounds complicated, but it's not really.
Pete