In Excel, I have a table with data. I use PowerQuery (Data --> From Table/Range...) to manipulate the data from the table and arrive at a set of data that I want to count (PowerQuery --> Transform tab --> Count Rows). This results in a query that is a single value (shows a '123' icon in the query list rather than a table icon).
How do I use that value in a cell on a worksheet?
I've tried a number of things, all of which seemed like they should work and didn't.
Like all other queries, it should by default (at least in Excel 365 for Windows 10) get loaded to a new Table when closing the Power Query Editor, like in this image of a query returning a single value:
If this is not happening automatically it may be because the query was loaded once already to a worksheet and then the Table or the worksheet was deleted or maybe the default query load settings have been changed, causing the query to now be loaded as a connection only.
In this case, click on the Data tab > Queries & Connections, which opens a pane on the right listing all the queries > right-click on the query name > Load To... which opens the Import Data window.
Note that this option is not accessible from within the Power Query Editor, the Close & Load To... button is greyed out for queries already loaded as a connection.
The Import Data window lets you load the query in a worksheet but only as a new Table or a new PivotTable in a new or existing worksheet:
To my knowledge, there is no way to load a single value query into a single cell directly without creating a Table.
One workaround is to load the query to a Table in a new worksheet, hide the worksheet, and refer to the Table's content wherever you need using a formula: =Query1. Note that named ranges contained in hidden worksheets do not appear in the Name Manager but they are visible in the formula autocomplete list as well as in the Go To list.
