Excel 365 for Windows is getting another option for importing data into PowerQuery, from a dynamic array.
In many cases the original data source could be imported, sorted and filtered in many different ways. Importing a existing dynamic array is a useful extra trick for us Excel nerds. We can think of three uses right away:
- Manipulating and transforming an array using the many options in PowerQuery.
- Exposing the dynamic array as a data source for other workbooks, more on that below.
- Converting a dynamic array into an Excel Table (a workaround but could be useful if a Table feature is desperately needed).
PowerQuery can already import a worksheet table or named range which can be a useful workaround to use the wonderful PowerQuery options. See Solved! The problem with Excel Tables and Transpose for just one example.
Microsoft announced this new option in a typically terse and somewhat unhelpful single sentence .
“You can now import, shape and refresh data from dynamic arrays in the current workbook.”
No mention of what’s doing the importing or where the feature is. Even just adding the word ‘PowerQuery’ would be helpful.
We dug around the current Insiders release and found it hiding under Get & Transform. The current ‘From Table/Range’ is renamed ‘From Sheet’ and includes a mention of array.
Once you’ve solved Microsoft’s little game of ‘hide the feature’, the rest is simple.
Select a cell inside the dynamic array, it doesn’t have to be the top-left starting cell.
Then choose Data | Get and Transform Data | From Sheet.
PowerQuery will open and import the array. Then you can do all the usual things with PowerQuery before either saving the connection or loading the results back into the workbook.
Using dynamic array in another workbook
As we mentioned earlier, importing an array into PowerQuery makes the array available to other workbooks. All Excel data connections can be linked to by another workbook.
Open another workbook and choose Data | Get & Transform | From File | From workbook, select an Excel workbook. PowerQuery will display all the available connections, tables and sheets including the connection we made from dynamic array ‘Passing Grade students only’.
It’s a neat trick. Instead of importing an entire table then filtering and sorting (possibly duplicating work done in the source workbook), now you can just import the finished list.
Updating isn’t automatic
Remember that Excel queries do not automatically update like normal recalculations or dynamic arrays. The query must be refreshed after the array has changed, otherwise the two will be out of sync. See Excel data connections auto refresh tricks.
Or use this little VBA code to force an update to all data connections whenever there’s a change in the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CutCopyMode = False Then
‘ do something like Workbooks(ThisWorkbook.Name).RefreshAll
.RefreshAll will update all data connections. If that’s too much of a load, there’s an alternative which only updates a single connection.
If you’re using the array sourced data connection in another workbook, you might want to ensure the query is updated before the source workbook is closed. Just to make extra certain that the dynamic array and query have the same, synced, results.
Who gets it?
Importing dynamic arrays into PowerQuery is in Excel 365 for Windows, Insiders Fast release v 2105 build 14014.20002.