Combining tables from files in a Sharepoint folder

Hi,

Fairly new to PQ with Excel and have run into a wall and would greatly appreciate some help.

Background

- Sharepoint folder holding up to 20 files with one table each with same structure, do not expect more than 40-50 rows each.

- I want to merge them into one master table for follow up purposes, frequency ones a week

Existing working solution

- One query per existing file using =Excel.Workbook(Web.Contents("full Sharepoint path to the file"), null, true)

- A Table.Combine({query1, query2, ...}) covering the existing files.
Drawback is that whenever there is a need to add a new file or rename them, a PQ query adjustment need to happen, which is not fitting the users experience level.

Wanted approach

- Ability to load all files within the Sharepoint folder

Challenge

- Org. credentials work fine when referencing single files, but not for the folder. Have tried to reference the site path, without success. I am even one of the owners of the site so should not be a constraint.

Alternative approach (clunky)

- Setting up an Excel table with the names of the files including the path and load the table to PQ. Next is then to get the tables from the files and combine them.

Challenge

- I lost a way fowrad on this  - generating queries from the table and then combining them, or is there another way?