Forum Discussion

damit23183's avatar
damit23183
Microsoft Employee
1 month ago

Advice Needed – Power BI Incremental Load from Latest Excel File in SharePoint

Hi, 

I am working on a Power BI report and need some guidance on the best approach for loading data from Excel files stored in SharePoint.

I have a SharePoint site (for example, https://my.sharepoint.com/sites/ocd) that contains multiple folders. I need to connect to a specific folder named ABC.

In the ABC folder, users will upload a new Excel file every day. The files will have the same structure and column names, but the filename will contain a date suffix. For example:

  • surveydata1_0713.xlsx
  • surveydata1_0714.xlsx
  • surveydata1_0715.xlsx

Each file contains that day's data only.

My requirement is:

  1. Connect to the SharePoint folder.
  2. Identify and load only the latest Excel file based on the filename/date.
  3. Import the data from that latest file.
  4. Append the new records to the existing dataset/table.
  5. When a new file is added the next day, Power BI should automatically pick up only the newest file and process it.

Essentially, if yesterday's file was surveydata1_0713.xlsx, today I would like Power BI to pick up surveydata1_0714.xlsx and append only the new data.

Since this is my first time implementing this type of solution, I would appreciate any recommendations, best practices, or potential pitfalls to avoid.

Thanks in advance for your help!

Need Help

8 Replies

    • damit23183's avatar
      damit23183
      Microsoft Employee
      1 month ago

      Hi, Thanks for quick response.

       

      But, where is the solution link as I see only username link.

      Thanks

      • djurecic's avatar
        djurecic
        Super User
        1 month ago

        Sorry! I've edited the post and added the link 🙂

  • damit23183's avatar
    damit23183
    Microsoft Employee
    1 month ago

    I am still looking for possible solution. I refered above link but didnt work out as it is mainly showing on how set up power automate flow than Power BI related content.

     

    Thanks

  • v-sathmakuri's avatar
    v-sathmakuri
    Community Support
    1 month ago

    Hi damit23183 

     

    Thank you for reaching out to fabric community. Sorry for delay in response.

     

    Since the client is using only Power BI Service (without Fabric), there isn't a built-in capability for Power BI to persist previously imported data and append only the newest file on each refresh.

     

    If all historical files remain in the SharePoint folder,  If the requirement is to process only newly added files and maintain historical data, Power BI alone cannot achieve this. Instead, the incremental data should be ingested from SharePoint using a data integration tool such as Microsoft Fabric Data Pipelines and stored in a persistent data source, such as a database or a Fabric Lakehouse/Warehouse. Power BI can then connect to that data source and refresh the report without reprocessing the Excel files.

     

    Thanks!!

  • v-sathmakuri's avatar
    v-sathmakuri
    Community Support
    1 month ago

    Hi damit23183 ,

     

    Could you please review the solution provided above and let us know if you have any further questions.

     

    Thanks!!

    • damit23183's avatar
      damit23183
      Microsoft Employee
      1 month ago

      no it didnt work. 

      Reason: My client does not have Fabric and we dont have access to backend database either. Therefore, I am trying to find solution which can possible only in power query.

       

      Thanks