In Excel, how do I set up a defined named as the Source--not a Table--without generating errors? RRS feed

  • Question

  • I want to use a defined name that references a tabular area--not a Table--as the Source. To do that, I used the Editor to set up:

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

    However, after the query works successfully, I get an error that PQ couldn't find a table named "MyName". 

    Is there a way to define the Source so that Excel doesn't continually return those error messages after a successful query?


    Thursday, May 23, 2019 9:30 PM


All replies

  • Hi Charley. Can you elaborate a bit on the behavior you're seeing? When/where does it succeed, and when does it fail? Is the "tabular area" you're pulling from a named range, a PivotTable, or something else?


    Friday, May 24, 2019 10:45 PM
  • Hi, Ehren!

    The Excel name is a dynamic name that references part of a tabular pivot.

    So far, I'm in testing mode. And so far, the query is giving me correct results. But in the Queries & Connections pane, the successful query displays the yellow error icon. But when I click on the icon, the error usually goes away.



    Friday, May 24, 2019 10:59 PM
  • Hm. Interesting. Can you share a small sample workbook that demonstrates the issue?


    Friday, May 24, 2019 11:06 PM
  • I was afraid you were going to ask that. Let me see if I can find it again!



    Friday, May 24, 2019 11:17 PM
  • Ehren,

    Okay; I found it. But I can't make it fail. I'll update this message when it does.

    Years ago, by the way, I read a short story in Analog Magazine. Humans discovered a planet that was about 100 years behind us technologically. So the humans sold all sorts of high-tech gadgets to the people on that new world.

    But as soon as the humans left, the gadgets stopped working. 

    After a lot of testing, the humans figured out that we have a psychic ability that makes our high-tech stuff work. So when the humans left, everything failed.

    And they also figured out that people with more of that psychic ability tend to become problem solvers in their technical field...

    ...And THAT explains why broken software and equipment tends not to fail when the repairman shows up, or pays attention to the problem.

    So, Ehren, please DON'T think about this issue over the weekend so that I can make it fail again. If you do your part, I'll get back to you next week with an example that demonstrates the problem.



    Friday, May 24, 2019 11:39 PM
  • Hi Charley. I have been doing my best not to think about your issue. :) Any luck with the repro?


    Wednesday, May 29, 2019 5:58 PM
  • No, Ehren. I couldn't make it fail. But I'll be doing a lot with it in several weeks. And the closer I get to my deadline, the more likely it will fail. 

    So I probably get back to you then.




    Thursday, May 30, 2019 6:47 PM