Welcome to Excel Jet Consult blog post. In this video Excel tutorial, we learn how to use the From Sheet to import Dynamic Arrays Result to Power Query. This feature is currently available in the Beta Channel of Excel for M365 & Office365. Do watch the video in the link below and hit the subscribe button.
Video
Sample Data
In the caption below, we can see that we have filtered the main dataset using the FILTER Dynamic Array function based on the selected values in cells N2 and O2 respectively.
Our goal is to use the new From Sheet connector in the Get & Transform Data group
Import to Power Query
To import the Dynamic Arrays result to Power Query:
- Click inside the Dynamic Array result (either the top cell or the spill ranges is fine)
- In the Get and Transform Data group, click on From Sheet
Alternatively, after selecting a single cell in the Dynamic Array result, right-click and select Get Data From Sheet as we can see below
The Data will be imported to Power Query editor as we can see below
Load to Excel Table
To load the result back to Excel Table:
- Click on Close & Load To
- In the Import Data, Table and New Worksheet are fine, Click on OK
The beauty of this new feature is that if we selected another criteria from the dropdown list in cells N2 and/or O2, all we need to do is to come to the PQ result we imported into Excel Tables and refresh!
See you in the next blog
#Excel #DynamicArrays #FromSheet #PowerQuery