Forum Discussion

Eliot4's avatar
Eliot4
Frequent Visitor
3 years ago
Solved

Get excel file in sharepoint by today's date

Hello,

I want to get an excel file which is in a sharepoint folder by today's date.

My path is like this (obvisouly different in PowerBI source because it's in drive mode) : MonthlyReport/Year/Month/file.xlsx

The root MonthlyReport never changes. For example, today, when I'm opening my pbix file, I want to get the excel file which is stored in this path : MonthlyReport/2023/F062023/reportjune.xlsx and next month it would be MonthlyReport/2023/F072023/reportjuly.xlsx

Actually, the excel file name can changes and there may be several versions of the file in the month folder therefore I would like to get the most recent created file in the folder of the month. 
The structure of the excel files is the same, only values are changing. The excel file is an extract that's why I need to do that (I can't just retrieve the same file in drive mode)

I hope you'll find something to help me, it would be awesome ! Thank you very much

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi Eliot4 ,

     

    I'd do it something like this:

    let
    Source = SharePoint.Files("https://XXX-my.sharepoint.com/personal/XXX/", [ApiVersion = 15]),
    selectTodayFolder =
        Table.SelectRows(
            Source,
            each let
                todayDate = Date.From(DateTime.LocalNow()),
                todayYear = Text.From(Date.Year(todayDate)),
                todayMonth = Text.PadStart(Text.From(Date.Month(todayDate)), 2, "0")
            in
            Text.StartsWith(
                [Folder Path],
                "https://XXX-my.sharepoint.com/personal/XXX/Documents/MonthlyReport/"
                & todayYear & "/F" & todayMonth & todayYear
            )        
        ),
    selectLatestFile =
        Table.SelectRows(
            selectTodayFolder,
            each [Date created] = List.Max(selectTodayFolder[Date created])
        ),
    selectBinary = selectLatestFile{0}[Content],
    importWorkbook = Excel.Workbook(selectBinary),
    selectSheet = importWorkbook{0}[Data]
in
    selectSheet

     

    Pete

7 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

    Hi Eliot4 ,

     

    I'd do it something like this:

    let
    Source = SharePoint.Files("https://XXX-my.sharepoint.com/personal/XXX/", [ApiVersion = 15]),
    selectTodayFolder =
        Table.SelectRows(
            Source,
            each let
                todayDate = Date.From(DateTime.LocalNow()),
                todayYear = Text.From(Date.Year(todayDate)),
                todayMonth = Text.PadStart(Text.From(Date.Month(todayDate)), 2, "0")
            in
            Text.StartsWith(
                [Folder Path],
                "https://XXX-my.sharepoint.com/personal/XXX/Documents/MonthlyReport/"
                & todayYear & "/F" & todayMonth & todayYear
            )        
        ),
    selectLatestFile =
        Table.SelectRows(
            selectTodayFolder,
            each [Date created] = List.Max(selectTodayFolder[Date created])
        ),
    selectBinary = selectLatestFile{0}[Content],
    importWorkbook = Excel.Workbook(selectBinary),
    selectSheet = importWorkbook{0}[Data]
in
    selectSheet

     

    Pete

    • Eliot4's avatar
      Eliot4
      Frequent Visitor
      3 years ago

      Hello Pete, 

       

      It says to me for the output selectTodayFolder that the table is empty and therefore at the end "There weren't enough items in the enumeration to complete the operation"

      Do you know why ?

      • BA_Pete's avatar
        BA_Pete
        Icon for Super User rankSuper User
        3 years ago

         

        Difficult to say without seeing our actual implementation, but it's important that this Source step resolves to a table that contains all of the files on your SharePoint:

         

        Are you trying to navigate to a folder on a SharePoint site (page), or within OneDrive for Busines?

         

        Pete