How to load the results of a single-value PowerQuery into a cell in Excel?

Solution 1:

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.