Import multiple tables from excel file in dataflow gen 2

Hi Jesse_oudshoorn ,

Thanks for the reply from lbendlin .

I understand your need now, currently it is not possible to use Dataflow Gen2 refresh to write different tbl_ tables to lakehouse, more steps need to be done manually.

Here is the test I did, I stored the excel file named summarizeTable in SharePoint with 5 tables starting with tbl_.

Don't select the combine option, which will put us in a situation where we can only select one table, just click create.

You can see that the binary file inside content contains five tables.

Click on Advanced Editor and enter the code below, this will show you the table name and the corresponding table.

let
    Source = SharePoint.Files("your_sharepoint_site", [ApiVersion = 15]),
    ExcelFile = Source{[Name="your_table.xlsx"]}[Content],
    ExcelTables = Excel.Workbook(ExcelFile),
    FilteredTables = Table.SelectRows(ExcelTables, each Text.StartsWith([Name], "tbl_")),
    TableNames = Table.Column(FilteredTables, "Name"),

    // Define a function to process each table
    ProcessTable = (tableName as text) as table =>
        let
            TableData = Table.SelectRows(ExcelTables, each [Name] = tableName){0}[Data],
            PromotedHeaders = Table.PromoteHeaders(TableData, [PromoteAllScalars=true])
        in
            PromotedHeaders,

    // Iterate through each table to generate separate table objects
    Result = List.Transform(TableNames, each [Name = _, Data = ProcessTable(_)]),
    ExpandedResult = Table.FromRecords(Result)
in
    ExpandedResult

If you want to use a single table, you can click [Table] and add it as a new query.

Remember to rename the query and this is what the data will look like on display. Now you can set the destination to load it into lakehouse.

It's a workaround, and I understand it's a pain in the ass, but there's currently no automated process in dataflow gen2 that I'm aware of that automatically loads tables from different Queries into lakehouse.

If you want to write it all at once you can use this code in the advanced editor, but this is less intuitive and I don't think it's as good as the first method.

let
    Source = SharePoint.Files("your_sharepoint_site", [ApiVersion = 15]),
    ExcelFile = Source{[Name="your_table.xlsx"]}[Content],
    ExcelTables = Excel.Workbook(ExcelFile),
    FilteredTables = Table.SelectRows(ExcelTables, each Text.StartsWith([Name], "tbl_")),
    ExpandedTables = Table.ExpandTableColumn(FilteredTables, "Data", Table.ColumnNames(FilteredTables{0}[Data])),
  #"Removed columns" = Table.RemoveColumns(ExpandedTables, {"Name", "Item", "Kind", "Hidden"}),
  #"Promoted headers" = Table.PromoteHeaders(#"Removed columns", [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"ProductName", type text}})
in
    #"Changed column type"

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!