Accessing multiple files in our organizational SharePoint site

[latingraduate07]

I have several reports that require analysis for monthly audit purposes. The 9 files are located in our organization sharepoint site and all are formatted differently. All files need to combined into one file to conduct analysis via vlookup to different files--each file is generated and formatted individually for combining purposes.

There is one sheet in each file but they have specific sheet names (don't know if this matters).

All files are located in a path similar to this, however, the month's scope would be different (which is what I was hoping can create a parameter for it to grab upon editing the table with respective month's scope):

https://ORGNAME.sharepoint.com/sites/TEAM'S GRP NAME/Shared%20Documents/2.%20Performed%20Controls/ARC%20-%20JE/FY23/GA-4/07-July%202022/C-SAP/Raw%20Data/FILE1 NAME.xlsx

I would like to create a parameter that grabs the file path from a table RATHER than having to update each of the individually uploaded file's path. Also, the final combined table query is around 350K lines and fails every time right at 349K lines of retrieving data (so frustrating as it takes around 25 minutes to refresh).

Any assistance would be greatly appreciated as I've watched several tutorials and am unable to figure for a solution.

Thank you!

[Daryl-Lynch-Bzy]

Hi @latingraduate07 - you could consider starting with the following:

Source = SharePoint.Files( "https://ORGNAME.sharepoint.com/sites/TEAM'S GRP NAME" , [ApiVersion=15])

You will be represented with Table contain all the files in SharePoint.   You can now apply filters to the FolderPath and FileName columns to select the individual file.  Note this approach remove any Credential issues because access will be based on the SharePoint site rather than individual Excel file.

Filter = Table.SelectRows(Source, each Text.Contains( [FolderPath] , "ABC" ) and [FileName] = "xyx")

Use the Excel Workbook to open the Binary Filter.

OpenFile = Excel.Workbook(Filter)

It sounds like you need to find the relevant tables or sheets from each file rather than combinse.  But you should be able to use the Power Query to create a Reference or Duplicate query from the above steps.

Once you are happy, you may consider changing these into functions, so that you can combined multiples file across several months by removing the folder filter or use a parameter to change the month.

[latingraduate07]

Thank you so much for your response, however, I am a new Power Query user and have pretty much self taught via youtube videos.  I am not familiar with your solution ( I apologize).  If you are able to provide a step by step process, It would be greatly appreciated!