Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Connect to Excel File that replaces another with New File Name

Hi, I am wondering if there is some wildcard functionality which would allow me to connect to an excel file that is replaced in the same file path but with a different date? For example:

 

In folder X, PowerBI connects to the excel file named "Derivatives102523" , the next day, this file is replaced with an excel file named "Derivatives102623". PowerBI doesn't automatically connect to this file even though its the same file path because of the change in the end of the file name.

 

Is there a way around this so I don't have to keep updating the file in PowerBI Desktop and re-publishing?

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous - Yes there is.  If you connect to the excel file using the File.Contents - PowerQuery M | Microsoft Learn or SharePoint.Files - PowerQuery M | Microsoft Learn you will be provided with all the Excel files in the selected folder path.  This is presented in a standard table, so you can apply transformations to filename, or sort them by Date Modified.  The trick is then to select the lastest file by applying filters so there is only one left (the latest).  Then you can open the remaining file without including the filename.  Instead you select the file based on the Index:

    #"Open Excel" = Excel.Document( #"Previous Step"{0}[Content] )