Import Dynamic Arrays Result to Power Query

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.


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

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *