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!
