Forum Discussion

Theo_Westsite's avatar
Theo_Westsite
Frequent Visitor
2 years ago

Change Source from one Excel-File to multiple

Hello everyone,

I am very inexperienced with PowerBI and have only just started using it and therefore need your expertise:

PowerBI is currently extracting all data from a very large Excel file. Since this Excel file has become very large, I divided the file into different files by year manually.

 

This means that in future I will no longer extract the data from just one file with the name Data.xlsx but Data_2023.xlsx, Data_2024.xlsx, Data_2025.xlsx and so on. How to adust PowerBI / source settings so the divided files are been taken into account?

 

FYI:

- The subdivided files have the same structure as the original file Data.xlsx.

- The Data_2025.xlsx file does not yet exist. This is to be added next year, but I do not want to change the query then, but the file should be taken into account automatically as soon as it is available.

-If possible, i would prefer a solution where i only have to adjust the source settings but not to rebuild the entire query from scratch.

 

I have worked with a different BI system for many years. There, there was simply the option in the source to change the file path from Data.xlsx to Data_*.xlsx and all files would have been taken into account. Is there something similar in PowerBI?

 

Thanks for your support!

 

Need Help

5 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Power BI / Power Query can not create new files for you. Consider using Power Automate for example.

    • Theo_Westsite's avatar
      Theo_Westsite
      Frequent Visitor
      2 years ago

      Sorry for the confusion: i do not want PBI to divide or create the files. I just want to change the source settings so the divided files are been taken into account instead of just one file.

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Use Folder connector. I recommend to click transform data (not combine and transform).

    • Theo_Westsite's avatar
      Theo_Westsite
      Frequent Visitor
      2 years ago

      Thanks for the idea. The application was developed by my predecessor, who unfortunately I can no longer ask. The application references a SharePoint path in the PowerBI query. I can access the Sharepoint path via Explorer, but when I enter it in the query, an error occurs (I am constantly asked for my login details). When I click on browse during the query, only my personal file path appears (C:\..\...). How can i insert a Sharepoint point and make it work?

       

      Is this a Access right issue? If yes, then am i able to Access the Sharepoint path via Explorer?