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 🙂
Community ChampionHi stevelam9569
You can create a folder as source which allows you to have the main Excel file (i.e. historical to today) in the folder, then just add additional monthly Excel files in each month moving forward.
The benefit of this process is that you won't hit the 1,048,576 row limit that exists in Excel and, further, all of the transformations that you make on the initial file (which you can set as your Sample File) will be repeated upon refresh to every Excel file in the folder.
If the folder as source gets extremely big (e.g. I build one solution that had TXT files being generated every 15 mins with 456 columns and up to 200,000 rows of data in each, with a total of 28GB of data at the time), then you can create parameters so that the refresh process only takes into account the most recent month's data and leave all historical data loaded as it was in the model (eliminating refresh overload and related complications).
Hopefully that helps and makes sense. Happy to answer any other questions that you may have if needed.
All the best.
Theo
Hi Theo, thank you for the reply.
Your suggestion looks good. However, I a Power BI newbie and does not quite understand how to go about it. Do you have any link that describe the steps.
Just a bit clarification, currently, i need to update the Main excel file which have 5 worksheets in it.
Then all these all generating the charts and tables in the Power BI report. In the suggestion, i will need to change the datasource to point to a folder instead of the main excel file. Is that correct and how to do that in the data source setting ? Next you mentioned that every month , i will need to do the below ( just add additional monthly Excel files in each month moving forward. ) Are you saying, i created a new excel with the same headers and place into this new folder.
Thanks you for your kind assistance.
- TheoC
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.
- TheoC
stevelam9569 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!