Power Query where file name changes monthly

[duranimal86]

I have a Power Query setup to pull the data from a file saved on SharePoint, but each month the month # in the filename changes. How can i setup the query so that it always pulls from the file based on the rest of the filename that doesn't change. For example, last month it was "P07 Account List.xlsx" then still be able to refresh and automatically pull from "P08 Account List.xlsx" this month (i was unable to update my current query). So I just want to pull the PXX Account List.xlsx, no matter what month number is in the file name (there should only ever be one, but i could put in a sort on date created to get the most recent just in case), and then pull everything from the first sheet into a table in my excel file. I am able to navigate to the SharePoint folder and use the filters to get the list down to the "PXX Account List" file that i want, but then i can't figure out the next step of picking the sheet and pulling that into a table in my excel workbook without those steps using the current filename that won't be able to refresh when the name changes.

I have seen plenty of examples of having named cells in the file and getting the filepath and then entering the changing numbers to manually create the dynamic filename and pointing the source to that named cell, but there are a couple reasons that won't work for me. Because of a couple ways the query will be used, i need the dynamic selection to all be built into the query code and not require any manual input other than refreshing the query.

 

[SullyWYO]

I'm not familiar with Sharepoint, but Power Query can be set up to pull from a folder. I would suggest to organize the files into a folder. If the naming allows the files to be sorted by most recent, then you can just "Keep only Top Row" during the import steps so that you get the data from the most recent file.

 

[duranimal86]

I'm not familiar with Sharepoint, but Power Query can be set up to pull from a folder. I would suggest to organize the files into a folder. If the naming allows the files to be sorted by most recent, then you can just "Keep only Top Row" during the import steps so that you get the data from the most recent file.

Hey Sully, I have the query setup to pull from a folder and am using the sort/filter to identify the file that i want, but the issue is there aren't new files added to then just grab the most recent one, there are always the same 8 files in the folder and they are replaced each month with a new file and the month in the filename changes to the new month number. So i can't just pull the most recent based on creation date.

I am starting to think there is something very basic that i am missing. I am able to connect to the folder, filter/sort the files to get to the one that i want. But i can't figure out the next steps to select that file and then select the sheet without those steps being specifically associated with the workbook title, then not being to refresh the next month because the title of the workbook has changed. So even in your example of sorting and then using "Keep only Top Row" what would the steps be from that point to have it pull all of the data from the first sheet of that file without it hardcoding the workbook name into the step so that it could still be refreshed when the file name changed?

 

[SullyWYO]

How many sheets are in the file, and how are they named? Are you getting an error when you try to import that file?

I'm struggling to understand the problem - maybe a screenshot would help?