Still need help with Function to bring in new tabs from Excel

[craig811]

Hi all,

I am still struggling to create a function to bring in any new tabs created in an Excel workbook.

My template M Language looks like this:

let
Source = Folder.Files(#"Data Source"),
#"Filtered Rows to pick up correct file" = Table.SelectRows(Source, each Text.StartsWith([Name], "Accruals, Reaccruals")),
#"The must recent file" = Table.SelectRows(#"Filtered Rows to pick up correct file", let latest = List.Max(#"Filtered Rows to pick up correct file"[Date created]) in each [Date created] = latest),
#"Added Custom ""ExcelWorkbook""" = Table.AddColumn(#"The must recent file", "ExcelWorkbook", each Excel.Workbook([Content])),
#"Expanded ExcelWorkbook" = Table.ExpandTableColumn(#"Added Custom ""ExcelWorkbook""", "ExcelWorkbook", {"Data", "Item", "Kind"}, {"Data", "Item", "Kind"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded ExcelWorkbook", "Index", 0, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] = 1),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8"})
in
#"Expanded Data"

I was told if I add an 'index' cloumn and then create a function , I could then invoke the function based on the index value and it would then bring across the new tab. However, when I try and do this it just invokes and creates the function again.

Any help or ideas please thank you in advance.

Current Function M language which is not working as expected:

(index as number) => let
Source = (#"Data Source" as any) => let
Source = Folder.Files(#"Data Source"),
#"Filtered Rows to pick up correct file" = Table.SelectRows(Source, each Text.StartsWith([Name], "Accruals, Reaccruals")),
#"The must recent file" = Table.SelectRows(#"Filtered Rows to pick up correct file", let latest = List.Max(#"Filtered Rows to pick up correct file"[Date created]) in each [Date created] = latest),
#"Added Custom ""ExcelWorkbook""" = Table.AddColumn(#"The must recent file", "ExcelWorkbook", each Excel.Workbook([Content])),
#"Expanded ExcelWorkbook" = Table.ExpandTableColumn(#"Added Custom ""ExcelWorkbook""", "ExcelWorkbook", {"Data", "Item", "Kind"}, {"Data", "Item", "Kind"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded ExcelWorkbook", "Index", 0, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] = index),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Index", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8"})
in
#"Expanded Data"
in
Source

[lbendlin]

I assume the excel sheets all have the same format?  Would you like to provide a sample Excel file?

The usual approach would be

let
    Source = Excel.Workbook(File.Contents("C:\xyz\Data.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data",Table.ColumnNames(#"Filtered Rows"{0}[Data]))
in
    #"Expanded Data"