Trying to query a sheet in current workbook

Monsignor

Board Regular
Joined
May 30, 2011
Messages
162
My goal is to start a query with the source being the worksheet named ISS in the current workbook.

This page will be regularly updated and it's not formatted as a table. The purpose of the query is to cleanse and reshape this worksheet.

I've been successful in developing queries that refer back to the workbook and worksheet and have a long filepath name in the source. But if the workbook is saved to a new place, the queries breaks--even though the ISS worksheet is in the workbook.

Is there a way to point a query directly to the worksheet?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I did that with the other problem and it's working. With this one, my hope was that a worksheet in the same workbook would be easier to query as a source. A table within the same workbook can be sourced directly. Writing a formula in native Excel, referencing a different worksheet in the same workbook is easy.

If it's not possible to do this in M or PowerQuery, that has me wonder how this is all built and why a filepath can be worked around.
 
Upvote 0
Ok! I figured it out.

1. Convert the data on the sheet to a named range
2. Start a new query "from table"

BOOM! Even though it's not a table, the data in the named range lands in Power Query, as if it were a table, and is ready to be cleansed and reshaped as normal.

No more filepath.
 
Upvote 0
I was going at it a different way: I started with a blank query and typed "=Excel.CurrentWorkbook()" as the first line, then pulled in the data. When I look at the source in the "from table" query, it seems to be doing the same thing. I experimented with moving the file and it seems to work either way.
 
Upvote 0
kk thanks for the input. I was trying to start a fresh query from =Excel.CurrentWorkbook() but never got it going. But it sounds like we achieved the objective.
 
Upvote 0
Here's the full of what my coded ended up looking like with a named range called ISSRange:

Code:
let    
Source = Excel.CurrentWorkbook(),
    ISSRange = Source{[Name="ISSRange"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(ISSRange,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1", "Column8", "Column6"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column5", "Item Description"}, {"Column7", "On Hand"}, {"Column9", "On PO"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Column4] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Column3", "Column2"})
in
    #"Removed Columns1"
 
Upvote 0

Forum statistics

Threads
1,224,144
Messages
6,176,647
Members
452,739
Latest member
SCEducator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top