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 🙂
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.
Community ChampionHi 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!