Merge Excel files from SharePoint Online folder extracting multiple tables

Hi Catastasis,

You can certainly fetc hmore than one table at a time, please see the example below:

let
    Source = Excel.Workbook(File.Contents("C:\temp\test.xlsx"), null, true),
    
    Table1 = 
        let
            Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
            #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}})
        in
            #"Changed Type",
    
    Table2 = 
        let
            Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
            #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}})
        in
            #"Changed Type",
    
    Table3 = 
        let 
            Table3_Table = Source{[Item="Table3",Kind="Table"]}[Data],
            #"Changed Type" = Table.TransformColumnTypes(Table3_Table,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}})
        in
            #"Changed Type",
    Output =  Table.Combine ({Table3, Table2, Table1})

in Output

However, even in this case, from what I know, PQ does not prodive any guarantee that it will not fetch the binary file for each table. This is down to other factors around the code and how/where do you use the output.

Kind regards,

John

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.

Do the tables within each workbook has the same structure (columns)? If so, you can combine them in a single query. The Excel.Workbook function will show you all the tables, sheets, etc. in the workbook. You can filter on type table to keep just the tables, and then expand the column of tables to combine them. If you use the Combine & Transform feature and do those steps in the sample file transform, it should work.

Pat