One File in SharePoint Document Library

[jabrillo]

My Power BI dashboard retrieves data from a Excel file stored in a SharePoint document library. It is the only file stored there and it is updated weekly with a different file name. How can I set up Power Query so it will retrieve that file? I want to set up a Power Automate to run the data refresh when the file is replaced with a new file.

[jennratten · Accepted solution]

Hello - you could use a workflow like the one below with Power Automate.  Note, with a set-up like the one you have described, there are a lot of things that can go wrong, for example, what if there is ever more than one file in the folder?  If a file is added with a different name, what process is removing the previous file, etc.  That being said, here is a suggested flow, which includes some error preventing steps.

• Prerequisites
  • Use three folders that are each inside a parent folder.
    • Parent Folder
      • Folder 1: Incoming (new files should be saved to this folder)
      • Folder 2: Source (the file in this folder will be the source for Power BI)
      • Folder 3: Archived (old files are moved to this folder)
  • In Power Query, configure your query to get files from the Source folder and keep only the first row (first file).  Make sure your query does not reference any specific file name.
• Power Automate Flow
  • Trigger: when a file is created in the Incoming folder.
  • Action: Move all files in the Source folder to the Archived folder.
  • Action: Check the count of files in the Source folder to make sure it's zero.
  • Action: Add a date/time suffix to the end of the file name.  This will prevent the flow from failing if there is more than one file with the same name.
  • Action: Move the new file from the Incoming folder to the Source folder.
  • Action: Refresh the Power BI dataset.

[jabrillo]

Thank you

[jennratten]

You're welcome!  If my response answered your question please click "Accept as Solution".  Thanks!