Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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 🙂
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.
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!
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.
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
Hi,
Share the link from where i can download your Excel file.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |