Power bi report

[bzeeblitz]

I have teams channel and stored Excel files in date wise in January folder and inside the folder I have january darewuse Excel files and it's stored in the below way

Report 01.01.25.xls

Report 01.02.25 so on till today and every day we store the files so my question is I need to pull these data in power bi report and publish as report so how do I achieve this.kindly suggest 

[anmolmalviya05 · Accepted solution]

HI @bzeeblitz ,

Please upload all the files to a SharePoint folder and use the SharePoint Folder connector in Power BI.

1. When you enter the folder path, a preview of your data will appear.
   
2. Click Combine & Transform (ensure all files have the same structure).
3. Power BI will automatically merge data from all files into a single table.
4. In the next window, choose a sample file to define the structure.
   
5. Click OK, and Power BI will consolidate data from all dates.

Let me know if you need any further clarification! 🚀

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

Subscribe my youtube channel for Microsoft Fabric and Power BI updates: https://www.youtube.com/@AnmolPowerBICorner

[anmolmalviya05 · Accepted solution]

HI @bzeeblitz ,

Please upload all the files to a SharePoint folder and use the SharePoint Folder connector in Power BI.

1. When you enter the folder path, a preview of your data will appear.
   
2. Click Combine & Transform (ensure all files have the same structure).
3. Power BI will automatically merge data from all files into a single table.
4. In the next window, choose a sample file to define the structure.
   
5. Click OK, and Power BI will consolidate data from all dates.

Let me know if you need any further clarification! 🚀

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

Subscribe my youtube channel for Microsoft Fabric and Power BI updates: https://www.youtube.com/@AnmolPowerBICorner

[bzeeblitz]

Already I have developed report by creating bridge table for id columns for 2 tables inside excel  for yesterday which utilizes yesterday data but today when I refresh I see the power bi report has only today's data so how do I retrieve yesterday data in this scenario. Please let me know if you understood though I only maintain datsource fresh data on daily basis in consolidated SharePoint document library so watever report I created is from different folder which is daily folder in consolidated document library so my power bi is scheduled report from daily folder in the document library but then everyday I upload file with same structure and yesterday data is not present in this datsource SharePoint daily folder so my question is everyday historical data gets lost. Though I need help I'm maintaining everyday excel file in consolidated library and again I should apply bridge table to filter required tables after combining and then refresh going forward kindly advise

[lbendlin · Accepted solution]

Power BI has no memory. Don't overwrite the data on the SharePoint. Add new files daily and then ingest by combining them.

[bzeeblitz]

Ok so in that case we have more than lakhs of data per single day so is it recommended to use SharePoint for storage or dataverse for storing the same and combining etc

[lbendlin]

If you store the data in a simple format (CSV or Parquet) then the number of rows is not a problem at all. Excel files are suboptimal as they have such a big meta data overhead

[bzeeblitz]

I'm asking we are storing combined data at one place that's been extracted and finally consolidated data in SharePoint list right 

[lbendlin]

Please don't use .xls - it is a legacy format. Use .xlsx if possible.

Have you considered storing these files in a SharePoint rather than in Teams?

[bzeeblitz]

Yes it's xlsx .ok we store in SharePoint as per your suggestion but how to extract historical data in power bi report let's say

Report 01.01.25 xlsx

Report 01.02.25.xlsx ..... So on daily basis from specified path in SharePoint document library. As of now we are able to generate today xlsx file that is one xlsx file but we need to perform for historical data.kindly suggest