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…
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.
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.
Having ordered by data, I need to group it. I can do this using ‘Group by’ on the Transform tab.
I include a simple aggregation to count all rows:
I click OK to see my grouping.
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.
The M code I have used is:
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’.
I can now expand my column.
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’:
I click OK to view my data.
Now I can remove the original Name, rename my new column and remove the Row column I created to help me achieve my goal.
I have also reordered my columns to resemble the original table. I ‘Close & Load’ to Excel from the Home tab.
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!