Why cant my Excel detect all my PowerQuery connections?

[Iloveexcel&excelloveme]

Hi Gents/Ladies,

I noticed that some of my PowerQuery's connection is not detected by my new Workbook unless i load it onto the worksheet.

This is what i'm doing.

1) I keep all my source data separated from my main workbook. Currently, I've 5 diff data sets from the different workbook and I load it as Connection.

2) I've a main Workbook to connect to all 5 workbook's connection. And i realized not all connection can be detected unless i load it onto workbook.

Does anyone know what causes this? As i've workbook that have millions of rows and i can't load onto a worksheet due to Excel limitation.

Appreciate any insight on this (By the way, is separating the soure data from the main workbook a best practice?)

 

[Ken Puls]

I'm not following what you mean by it's not detected. Are you saying that you load to connection only, and you can't see them when trying to create Pivot Tables or charts? If so, that's by design.

Connection only queries can only be "seen" by other queries. For Excel to see them, they need to be loaded into either a table (giving you that row limit issue), or Power Pivot (which doesn't have the row limit issue.)

What version of Excel are you on? 2010, 2013 or 2016?