Import multiple tables from excel file in dataflow gen 2

[Jesse_oudshoorn]

Hi all, I have an excel file that is stored in a Sharepoint folder. I am trying to ingest all tables within this file that start with 'tbl_' into a lakehouse. I tried to do this with the Dataflow Gen 2 & Sharepoint folder connector, however, I am only able to select one of these tables at the time:

There will be more tables added into this file that start with 'tbl_' in the future and I would like to run a daily refresh on this dataflow as to get all these tables updated everyday. Is there a possibility to ingest all tables that start with 'tbl_' dynamically into the lakehouse via this way? Thanks in advance!

[Anonymous · Accepted solution]

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!

[Jesse_oudshoorn]

Thanks! This works like a charm. I went with the first solution you provided and it gives me what I need! 

[lbendlin]

Look at the Advanced Editor code - you will see the individual navigation steps.  Remove the step that chooses a sheet.  Then add a filter to select the desired sheets (the ones starting with "tbl_"  (which is an unfortunate name for a sheet, btw) and then create your own ingestion routine that you can apply to each of them. Finally combine the resulting tables.