Over 1300+ Excel spreadsheet reports .csv - best way to load and use in PowerBI report

[dd88]

Reports transitioned and inherited to our team/myself from another department.

Call data reports, which tracks all calls coming into and outgoing in the company. inbound and outbound calls.

The reports are broken down by time frames ie
0000 - 6000
0600 - 1200
1200 - 1800
1800 - 0000

Every day receive a minimum of 2 reports for AM & PM.  0600 - 1200 and 1200 - 1800
Can be a total of up to 3-4 received reports each day that includes all timeframes.

Format is Excel spreadsheets .csv files. Each spreadsheet report contains 1 tab of data.

Example of standard file names are:

TCO365_CDR_<companyName>_N3816865R_20241218_0600-1200.csv
TCO365_CDR_<companyName>_N3816865R_20241218_1200-1800.csv
TCO365_CDR_<companyName>_N3816865R_20241218_1800-0000.csv

To manage the files I have organised into year folders, and uploaded into SharePoint site/folders eg

\Call data\

2025.  Total spreadsheets. still in progress

2024.  Total spreadsheets 753

2023.  Total spreadsheets 542

2022.  Total spreadsheets = 26

Overall total spreadsheets = 1321+

I need to create a PowerBI report on these reports. To display summaries eg total calls in a month, total inbound calls, total outbound calls, for each month, in the year and for each year etc.

What is the best way to upload these files to use in PowerBI reports.

Note it maybe better to load into a database table in Fabric. perhaps not now. perhaps as stage 2

any info on this one?

TIA

[dd88]

Thank  you @Thejeswar  @suparnababu8 

[Thejeswar]

@dd88 ,

You can try using the Folder connector to load all the files in the folder at once. In this case since you have folders structed by year, post upload you will have 4 tables (one for each year). 

Then you can combine these and work on your visuals.

Since you are uploading using folder connector, any new files added to these folders will automatically be brought into the Power BI report with a simple report refresh 

If this helps, mark it as a solution and appreciate with a kudo!

Regards,

[dd88]

Thank you @Thejeswar 

Folder connector only works for only for local or Network Drives folders.

The folders are online in SharePoint.

Many thanks again ..

Thanks

[Thejeswar · Accepted solution]

Hi @dd88 ,

You can use the Sharepoint Folder Connector to do this if your files are in Sharepoint. The Below link tells you how you can use this connector

Power Query SharePoint folder connector - Power Query | Microsoft Learn

Regards,

[suparnababu8]

Hello @dd88 

1) If the all excel files structure column same, you may use sharepoint folder connection. While loading first time you can use combine&load option. Then go to Power Query editor, promote use first row as header if your datasets has no headers. 

Now whenever a new file will be added to your sharepoint folder, once you refresh the report newly uploaded data will be automaticlly appear in you Power BI report.

2) I fthe data size is going to become a huge data in future, you may go with Fabric OneLake solution. It's depend on your data size increasing in future.

If you need additional info pls go through the below ursl

https://addendanalytics.com/blog/import-multiple-excel-files-in-power-bi-tips-tricks

https://youtu.be/9sfCDCpWTfc?si=I1_VnsZ6seh5UiO_

Thanks!