Forum Discussion

Catastasis's avatar
Catastasis
Regular Visitor
3 years ago

Merge Excel files from SharePoint Online folder extracting multiple tables

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 ...
Tips and Tricks
Tutorial Requests
jbwtp's avatar
jbwtp
Memorable Member
3 years ago

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