Forum Discussion
Splitting a large data souce Excel into few smaller excel
Hi stevelam9569
No worries at all mate. Firstly, here are a couple of links for you to look at:
- https://powerbi.tips/2016/06/loading-data-from-folder/
- https://docs.microsoft.com/en-us/power-query/connectors/folder (just go down to the subtitle "Connect to a folder from Power Query Desktop" and read up on the steps.
Alright, now to answer a few of your questions with answers (or additional questions lol):
- With regard to the five sheets in your Excel file, are they the identical structure? Or is each sheet unique and for a different purpose?
- I believe that because you are changing the data source from a single source to a folder as source, you will likely need to do a bit of rework.
- Regarding all of the new Excel files each month, all you will need to do is literally save the Excel file down (with the same headers, etc.) to the folder that you are using as a source. Then all you need to do once you save the file in the folder is go and press the refresh button. And that's it.
Let me know if that makes sense or if you need further clarification.
Theo 🙂
Thanks Theo, I will go over the explaination and links you provided.
(With regard to the five sheets in your Excel file, are they the identical structure? Or is each sheet unique and for a different purpose? ------- Different column headers and for different purpose and reporting )
Appreciated your kind help.
Community Championstevelam9569 okay, that's great to know regarding the 5 sheets. I recommend separating the one sheet with the lots of data into its own separate file. Keep the other 4 sheets as sources so that you don't need to do rework on those. This way, the only rework you would do is with the single file that has lots of records.
Best of luck with it all mate!