Please note javascript is required for full website functionality.


Power Query: Expendable Expand

18 January 2023

Welcome to our Power Query blog.  This week, I investigate how to avoid expanding data after merging.


In Join or List Part 1, I merged queries to get all the data I needed in one table.  I had two queries, Expenses and Permissions:

I merged the tables to find out who has expenses that are not allowed or that require more information.  I started in Expenses, and I chose ‘Merge Queries’ from the Home tab:

This enabled the ‘Merge Queries’ dialog:

I matched the data on Expense:

I used the icon in the Permissions column to extract the data from the tables:

I only needed the information in Column2.

I had the data I needed, and I renamed the column Allowed to Claim?  However, did I need to expand the data in Permissions to achieve this?  There is another way I could have extracted the data:

Let’s revisit.  I am starting at the ‘Merged Queries’ step:

I choose to add a ‘Custom Column’ from the ‘Add Column’ tab:

I call the new column Allowed to Claim?, as above:

The M code for this is:

= Table.FirstValue(Table.SelectColumns([Permissions], {"Column2"}))

This is made up of two steps.  Since Permissions is a column of tables, the first step is to use Table.SelectColumns() to extract only Column2 from each table.  However, Table.SelectColumns() will return a table with one value in it.  In order to extract the data, I use Table.FirstValue() to get the data in the first column and row. 

This merely extracts the data from the column.  I could also use table functionality to create columns using conditions that compare the data in the query with the data in the table.

The M code here has been extended to:

if (Table.FirstValue(Table.SelectColumns([Permissions], {"Column2"})) = "No") then "Requires Intervention"

else null


I am creating a new column which contains ‘Requires Intervention’ if the salesperson has tried to claim a restricted item.

In this case, I have avoided expanding the Permissions tables and created a column that uses a condition using the data in Permissions.  I would of course need to set the data type for my new columns, but this technique can save me from extracting columns that I don’t need to include in the query.


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