Hi, I just created another data connection that is the location of where all the excel files are stored. I use a normal load statement and the end looks like this. The "table is [For Upload]" part specifies the worksheet name. It load that worksheet from each excel file.
FROM [lib://Budget2020/2020 Budget Template*.xlsx] (ooxml, embedded labels, header is 2 lines, table is [For Upload]);