Merge Excel files from SharePoint Online folder extracting multiple tables

[Catastasis]

Hello,

I have a SharePoint Online Folder with monthly excel files. I have found plenty of explanations how to merge the excel files with the SharePoint Online Folder connector from Power BI. But now comes my special requirement. All resources only use one Table from the merged Excel files. I have multiple tables to extract from the merged excel files. I know I can copy the same query multiple times and extract the diffrent tables. But that slows down the refresh. 

The question is if there is a way to use the same Source (SharePoint Folder) multiple times and locally extract the tables needed?

Kind regards

[ppm1]

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

[Daryl-Lynch-Bzy]

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.

[jbwtp]

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