Excel.CurrentWorkbook() sees a table that is not visible in the source. Namespace problem?

[solarpolygon]

I have a Source command in a PowerQuery that looks like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

I do not see a table named "Table2" in the Workbook. If I change "Name" to any other table name I know exists in the workbook, I get an error.

When I try to create a new Data Source in another workbook and choose Excel Workbook and then this particular workbook as the data source, it shows me all the tables I know exist, plus this "Table2".

It gets weirder. I saved a copy of the original workbook, then deleted the query in the copy. Now queries I believed to be using the original workbook as the data source have stopped working, claiming the source worksheet "Table2" does not exist.

What have I misunderstood about the way PowerQuery works?

[v-stephen-msft]

Hi @solarpolygon ,

You can check your table names in name box:

Reference: Rename an Excel table

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[solarpolygon]

Okay, there I see the table in question. If I select it, Excel jumps to a table that has a different name on the tab. Why is there this difference in the naming?

[Ehren]

Maybe you have two names defined for overlapping or identical ranges?

[Ehren · Accepted solution]

What do you see if, after opening the workbook that apparently contains the mystery table, you click Formulas -> Name Manager?

[solarpolygon]

Okay, there I see the "Table2", which refers to a subregion of the Worksheet that excludes the headers.

I've been using Excel a long time, but this is the first time I've seen Name Manager. I guess I'd better read up on it 🙂

[v-stephen-msft]

Hi @solarpolygon ,

Maybe you confuse Sheet and Table.😀

When you connect the excel file, you will see the tables and the sheets, and the tables are contained in the sheet.

If your problem is solved, please mark the helpful reply as the answer, and more people will benefit.

Best Regards,

Stephen Tao