Get excel file in sharepoint by today's date

[Eliot4]

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

[BA_Pete · Accepted solution]

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]

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]

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

[Eliot4]

Yes I can see my table with all the files including my excel target files. I'm trying to navigate to a Sharepoint site. 

[BA_Pete]

Ok, can you select your Source step in PQ and take a small screenshot (headers plus 3 or 4 rows) of the table that displays at that point please? You can blur out file names etc, I just want to check the column names and content types.

Then can you copy the code from Advanced Editor for the whole query as you're trying to implment it and paste it into a code window ( </> button) here please? Use the XXX anonymisation on the file paths if you want, but keep every path step intact (so all / ~ / ~ steps) and leave as much visible as possible in line with your security needs. This is so I can check that the code and URLsare aligned properly for your scenario.

Pete

[Eliot4]

Thank you so much it's working now thanks to your comments. I was leaving %20 for the space in the folder path. Thank you for your time !

[BA_Pete]

No problem, happy to help.

Please mark the solution that's worked for you and don't forget to thumbs-up any posts that have helped you 👍

Pete