Splitting a large data souce Excel into few smaller excel

[stevelam9569]

Hi, I have large excel which is updated every month and it is growing .  I wanted to split the excel into few smaller excel based on the Year of the data . However , I not sure how to do it for my Power BI report which currently all the pages of report are pointing to a single excel Table. Please kindly help. Thanks

[TheoC]

Hi @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

[stevelam9569]

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 · Accepted solution]

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):

1. 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?
2. 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.
3. 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 🙂

[stevelam9569]

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!

[stevelam9569]

Thank you Ritesh.

Any other method to reduce the Master excel size so that each month I do not have to upload a very large excel into the power BI

[ribisht17]

@stevelam9569 

1. You can split the master excel sheet into multiple excel sheets

2. Then you can union the excels and connect to the Power BI report

I am not sure about the advantage though, here are a a few tips to help you

Best practices when working with Power Query

Data reduction techniques for Import modeling

Power BI – Quick Tips working with multiple large CSV Files

Regards,

Ritesh