When my data source has a name with a date (for instance, an excel file with name: test – 01.01.2026.xlsx) and every month, in the sharepoint folder, either I have to replace it with the last month or I will upload the last month file, in both situations, my dashboard will stop to work and to resolve it, I will have to update it manually. In this article, I will explain different ways to do it automatically and I will have to edit the code in “home -> advanced editor” in Power BI Query:

Before to start, in the original code, copy what I highlight in green:

IMPORTANT: make sure that the file starts with an unique name because if in the folder, there are other files and subfolders which start with the same name, it will not work properly. For instance, in the same folder:

• I have a subfolder with name “test” (conflict)
• I have a file with name “test for unix.xlsx” (conflict)
• I have another file with name “check test ticket.xlsx” (all good)

In this case, in the code, I will reference my file as “test –“ (unique) and not “test” (conflict)

NOTE: after changing the code, do a refresh by clicking on “home -> refresh preview” if not, the updated new data will not display

Option 1: only 1 single file

I have one unique file meaning that every month, I will replace the old one.

let
    // change www by yours
    FolderSource = SharePoint.Files("https://www",[ApiVersion=15]),
    // change xxx (no need to put the full file name) and yyy by yours
    FileSource = Table.SelectRows(FolderSource,each Text.StartsWith([Name], "xxx") and Text.Contains([Folder Path], "/Shared Documents/yyy/")),
    Source = Excel.Workbook(FileSource{0}[Content], null, true),
    // change zzz by yours
    DataSource = Source{zzz}[Data]
in
    DataSource              
              

Option 2: fetch the last one (current month)

I have multiple files so it will update the last one which is the current month. For instance, the current month is June.

let
    // change xxx (put the file name until the date) by yours
    FileName = "xxx" & Date.ToText(DateTime.Date(DateTime.LocalNow()), "dd.MM.yyyy") & ".xlsx",
    // change www by yours
    FolderSource = SharePoint.Files("https://www",[ApiVersion = 15]),
    // change yyy by yours
    FileSource = FolderSource{[Name = FileName, Folder Path = "https://yyy"]}[Content],
    Source = Excel.Workbook(FileSource, null, true),
    // change zzz by yours
    DataSource = Source{zzz}[Data]
in
    DataSource             
              

Option 3: fetch the last one in the list

I have multiple files so it will update the last one in the list and not the current month. For instance the last one is March and currently, the month is June and the current month file is not in the sharepoint.

let
    // change www by yours
    FolderSource = SharePoint.Files("https://www",[ApiVersion = 15]),
    // change yyy by yours
    FileSource = Table.SelectRows(FolderSource, each [Folder Path] = "https://yyy"),
    // change xxx1 (no need to put the full file name) by yours
    FilterFiles = Table.SelectRows(FileSource, each Text.StartsWith([Name], "xxx1") and Text.EndsWith([Name], ".xlsx")),
    // change xxx2 (put some characters of the file name before the date) by yours
    AddFileDate = Table.AddColumn(FilterFiles,"FileDate", each Date.FromText(Text.BetweenDelimiters([Name], "xxx2", ".xlsx"),[Format = "dd.MM.yyyy"]), type date),
    Source = Excel.Workbook(Table.Sort(AddFileDate, {{"FileDate", Order.Descending}}){0}[Content], null, true),
    // change zzz by yours
    DataSource = Source{zzz}[Data]
in
    DataSource               
               

Option 4: fetch the last updated one based on the sharepoint modified date

I have multiple files so it will update the last modified one in the list. For instance the last one is March and the current month (June) is not in the sharepoint but I updated data of February.

let
    // change www by yours
    FolderSource = SharePoint.Files("https://www",[ApiVersion=15]),
    // change xxx (no need to put the full file name) and yyy by yours
    FileSource = Table.SelectRows(FolderSource,each Text.Contains([Folder Path],"/Shared Documents/yyy/") and Text.StartsWith([Name], "xxx")),
    SortedFiles = Table.Sort(FileSource,{{"Date modified", Order.Descending}}),
    Source = Excel.Workbook(SortedFiles{0}[Content], null, true),
    // change zzz by yours
    DataSource = Source{zzz}[Data]
in
    DataSource