Trying to query a sheet in current workbook

[Monsignor]

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?

 

[anvg]

Hi
Please, try to study that solve http://www.mrexcel.com/forum/power-bi/906792-file-path-problems-sharing-power-query-solutions.html
Happy New Year!
Regards,

 

[Monsignor]

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.

 

[Monsignor]

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.

 

[kkardynalski]

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.

 

[Monsignor]

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.

 

[Monsignor]

Here's the full of what my coded ended up looking like with a named range called ISSRange:

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"