Loading from Excel: How can I extract the sheet name of excel tables?

[DavidBenaim]

I am using Power Query in PowerBI with a source of an Excel file. Many of my Worksheets have 2 Excel Tables, and I want a way to map each table to the worksheet it lives in. The Default Power Query experience has different rows for tables & sheets, but I want to see an extra column where each table is mapped to a worksheet. See screenshot below. Any ideas?

Sheets and tables are both shown at the same granularity level, could I somehow map tables to the sheets its on?

[DavidBenaim]

The file is here: https://www.dropbox.com/s/avvb0owvyhs9dj7/Demand%20entry%20form.xlsx?dl=0

In that I also have a query "Get data>From workbook" where I only show the first "Source" step and load that to the worksheet.

Thanks for your help 🙂

[Ashish_Mathur]

Hi,

This M Query works

let
    Source = Folder.Files("C:\Users\Ashish\Desktop\Data"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"})
in
    #"Expanded Custom"

Look at the first column - that is your sheet name.

[DavidBenaim]

Thanks, the Power Query shared is what I normally do, but it doesn't show the sheet name of the relevant tables, it has table names in rows, not columns.

VBA seems to be the solution - so thanks for sharing the code!

[MarcelBeug]

You get the names of your tables and the names of your sheets, but you don't get the name of the sheet with your table objects.

I don't think it is possible in Power Query to get the data which table is on which sheet.

I would suggest to try and create such a table with VBA.

[MarcelBeug · Accepted solution]

VBA code to list all sheet/table combinations with output written to worksheet "SheetTableList" (make sure to create this worksheet first).

Original VBA code: http://www.ozgrid.com/VBA/shapes.htm

Sub SheetTableList()
    Dim sobject As Object, lLoop As Long
    Dim Ws As Worksheet
    Dim wsLoop As Worksheet
    ''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''ADJUSTED CODE FROM:'''''''''''''''''''
    ''''''''''LIST PROPERTIES OF SHAPES'''''''''''''
    ''''''''''Dave Hawley www.ozgrid.com''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''
    Set Ws = Sheets("SheetTableList")
    Ws.Cells.Clear
    'Add headings for our lists. Expand as needed
    Ws.Range("A1:B1") = Array("Sheet Name", "Table")
    'Loop through all Worksheet
    For Each wsLoop In Worksheets
        For Each sobject In wsLoop.ListObjects
                'Increment Variable lLoop for row numbers
                lLoop = lLoop + 1
                With sobject
                    Ws.Cells(lLoop + 1, 1) = .Name
                    Ws.Cells(lLoop + 1, 2) = wsLoop.Name
                End With
           Next sobject
      Next wsLoop
    'AutoFit Columns.
    Ws.Columns.AutoFit
End Sub

[Ashish_Mathur]

Hi,

Share the link from where i can download your Excel file.

[mehaboob557]

Hi @DavidBenaim,

Can you share me the excel ?