Power Query: Handling Dynamic Arrays

Welcome to our Power Query blog.  This week, I extract data from a Dynamic Array.

I have a list of trainee salespeople that I need to extract to Power Query, so that I can merge their details with another query:

Seems simple enough; I select the data and use ‘From Table/Range’ on the ‘Get & Transform’ section of the data tab:

I don’t have any headers, so I take the defaults.

The results are not good.  If I click on the Error value, I see this:

If I discard the query and go back to the sheet, I can see what has happened:

To get a #SPILL! error, I must be dealing with a Dynamic Array.  I use CTRL+ Z to undo the Table creation. 

The list has been created using FILTER() which means that the output is a Dynamic Array.  Obviously in this example, I can just use the original table, but let’s assume that I don’t have access to that.

I used ‘From Table/Range’ to extract the data, and so Power Query converted the data to a Table, which doesn’t currently work with Dynamic Arrays.  I could however create a range. 

In the ‘Name Manager’ on the Formulas tab, I can create a new range.  If I just select the cells currently populated, this will be incorrect when I have more trainees.  I need to create a dynamic range.  This is the range I create:

The Excel formula is:

This links to the data in the Dynamic Array created by the formula in cell $F$2.  Now, I can create a blank query to access this range:

I create the Source step:

The M code is:

This extracts the content from the range DR_Trainees in the current workbook.

Power Query generates a ‘Changed Type’ step, which I keep.   

I don’t rename the columns yet, as I want to check that this query will refresh as expected.  I use ‘Close & Load’ from the Home tab and create the output on a new sheet:

Now I can add some new trainees to the original table:

The Dynamic Array updates immediately.  I refresh theTrainee_list query:                     

The new trainees appear, and my list is complete.  I can use Power Query to extract data from a Dynamic Array. 

Come back next time for more ways to use Power Query!