Merge Excel files from SharePoint Online folder extracting multiple tables

Hi Catastasis - if I am following correctly, it sounds like you need to use a dataflow to read a single table from multiple excel files into a single table (i.e. a single csv file in dataflow).   Then take the dataflow table an split into different tables.  For example to create a fact table and dimension tables.  If this is the case, I would definitely use a dataflow to extract and consolidate the data in one place, then the second (or your Dataset/pbix) to do the transformations/load.  I also recommend using Power Automate to trigger dataflow refresh in sequence.