Power Query: Handling Dynamic Arrays
8 March 2023
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!