Power Query: Empty
11 March 2020
Welcome to our Power Query blog. This week, I look at how to achieve the opposite of fill up / down.
Maureen is in charge of my imaginary salespeople. She has been looking at the following data and she has a request…
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
Maureen doesn’t want to see the salesperson’s name on each row, she wants to only see it on the first row for that salesperson. She’s not interested in PivotTables!
I begin by extracting the data to Power Query using the ‘From Table’ option on the ‘Get & Transform’ section of the Data tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
Since I want to essentially split my data into groups under each name, I start by sorting by name using the filter next to Name.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
Having ordered by data, I need to group it. I can do this using ‘Group by’ on the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I include a simple aggregation to count all rows:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I click OK to see my grouping.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I now need a method of linking all the rows with the same name, so I use an index column. I want to effectively add an index column to each table in Name_Count so I do this by adding a ‘Custom Column’ from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
The M code I have used is:
= Table.AddIndexColumn([Name_Count],"Row",1,1)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I can see that all my information is in my new Custom column, so I can remove the other columns by selecting Custom, right-clicking and choosing ‘Remove Other Columns’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I can now expand my column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I am almost there. I only need to show the value in Name if Row is one (1). There are several ways to do this, but I will add a ‘Conditional Column’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I click OK to view my data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image12.png/a1537847463e660a31158c8032525438.jpg)
Now I can remove the original Name, rename my new column and remove the Row column I created to help me achieve my goal.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image13.png/917da985be13220165c8d2823e95344f.jpg)
I have also reordered my columns to resemble the original table. I ‘Close & Load’ to Excel from the Home tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/171/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I have emptied the other cells in the column so that the data is formatted for Maureen.
Come back next time for more ways to use Power Query!