powerbi parse sheets in excel file

I'm importing a list of excel files into PowerBI. So far, I've imported the folder, expanded the files to show the list of sheets, and expanded the sheets to show columns.

Here is my code so far

let
Source = SharePoint.Files("https://westfaliatech.sharepoint.com/sites/DynamicsBusinessCentralUpgrade", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "WTI-Test")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Test", each Excel.Workbook([Content])),
#"Expanded Test" = Table.ExpandTableColumn(#"Added Custom", "Test", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Test.Name", "Test.Data", "Test.Item", "Test.Kind", "Test.Hidden"}), //expand each file 
#"Filtered Rows1" = Table.SelectRows(#"Expanded Test", each [Test.Item] <> "Scenario List"),
#"Expanded Test.Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Test.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}), //expand each sheet
#"Filtered Rows2" = Table.SelectRows(#"Expanded Test.Data", each ([Column1] <> null))

in #"Filtered Rows2"

Is there a way to insert steps where each sheet is expanded? I want to set a calculated column where the value is a specific cell in each sheet in each file. For example, I want to set the SheetName column to the value in cell B2 for the rows in that sheet.

Solution 1:

At the end of your script, I would add a Custom Column step to refer to a cell from the previous step's results, e.g.

#"Add SheetName" = Table.AddColumn(#"Filtered Rows2", "SheetName", each #"Filtered Rows2"[Column2]{1})

Hopefully the references are mostly obvious based on your script. I'm assuming column B in excel becomes Column2 in your query. The obscure part is the row reference {1} - it's a zero-based row number, so row 2 in Excel becomes {1}.