Excel data sources, best practise

[sanderson2008]

I have monthly reports that contains between 120k-150k records per month.

So in 1 year 1.8m records. i have multiple years of data.. 3-4 years.

My report will have a data viz. showing the summary of usage per year on a monthly timeline.

My question is. Should i keep the records in:

1. one excel file per month or

2. consolidate each month's data into one worksheet in one exel file or

3. one worksheet per month (effectively creating a table) into one excel file?

Or should consider placing into a SQL file?

What would be the best approach and why? Thanks!

[amitchandak]

@sanderson2008 , from where the source data is coming. If the source data is excel, prefer to have excel in a folder of SharePoint. and you can try the following ways.

https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

https://medium.com/@wkrzywiec/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4...

For merge

https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...

In case the source data some kind o database you can move that to Azure SQL DW or Azure Synapse. There is a lot of options post that how to work with Power BI. Direct query/ imports etc. 

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/get-started-power-bi-professional

[Anand24]

Hi @sanderson2008 ,

I completely agree with what @Greg_Deckler has suggested.

It's always better to keep the files in database such as SQL server or any other than simply using excel file.

If you are anyway going to fetch from excel, then option 1: one excel file per month is the best option so that all the data in single files are resulting to less size.

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Greg_Deckler]

@sanderson2008 - You could do all of those things. But, for that many records, I would highly consider loading the information into an Azure SQL database or something like it. That's just a lot of information to be storing in Excel files. If this is impossible, I thinking that 1 Excel file per month would be a good route and lend itself to a Power BI Folder query. That would keep the Excel files to a reasonable size and avoid manual work consodidating them.