Back in episode 2359, I needed to add a blank row after every group of names. Although I was already in Power Query, I went out to Excel to add the blank rows.
Several people commented with code to add a blank row after each group in Power Query. Thanks to @BillSzysz1 @radosawpoprawskiyourfriend769 @GeertDelmulle Rico S and Suat Ozgur for guiding me on how to add one line of M code to my query.
I am at that stage in my Power Query journey that I prefer to do everything in the Power Query editor. Once I switch over to the advanced editor, I try to clean up the step names. And then I add in one line of M code before heading back to the Power Query Editor to finish the task.
Table of Contents
(0:00) Question: Insert blank row after each group in Excel
(1:14) Edit in Power Query Editor Group by Name with All Rows
(1:53) Advanced Editor - cleaning recorded M Code
(2:35) Power Query Table.InsertRows
(3:34) More cleaning in Power Query Editor
(4:10) Need to learn M for super Power Query skills
(4:50) Wrap up with Wally & Nancy
Several people commented with code to add a blank row after each group in Power Query. Thanks to @BillSzysz1 @radosawpoprawskiyourfriend769 @GeertDelmulle Rico S and Suat Ozgur for guiding me on how to add one line of M code to my query.
I am at that stage in my Power Query journey that I prefer to do everything in the Power Query editor. Once I switch over to the advanced editor, I try to clean up the step names. And then I add in one line of M code before heading back to the Power Query Editor to finish the task.
Table of Contents
(0:00) Question: Insert blank row after each group in Excel
(1:14) Edit in Power Query Editor Group by Name with All Rows
(1:53) Advanced Editor - cleaning recorded M Code
(2:35) Power Query Table.InsertRows
(3:34) More cleaning in Power Query Editor
(4:10) Need to learn M for super Power Query skills
(4:50) Wrap up with Wally & Nancy
Transcript of the video:
Power Query, -Insert a Blank Row After Each Group. All right, so a few weeks ago, this video, 2359.
I was cleaning a fixed-width text file in Power Query.
And when I got to the end, they wanted a blank row after every group of names, and I just had to go out to Excel to do that.
But several people, Bill Szysz, Radoslaw Poprawski, Geert Delmulle, and Rico all said, "Now, hey, here's a code that you can do that".
So I turned to my friend, Suat Ozgur, who's writing this book, You Wouldn't Write Professional VBA Code Using the Macro Recorder, Would You?: A Programmer's Guide to M. So I'm going to do a hybrid approach here.
I'm going to try and do as much of this in the Power Query editor as I can, but the one line where we add those rows is going to have to be some M code, all right?
So we start out with data here.
My goal is to get all the Andy records together and then after Andy, a blank row.
Is that a good idea to do? No, right?
But the person who I ran into who had the file, that was how they needed to produce the data.
So I don't want to hear that we shouldn't have blank rows in our data.
It was a requirement, and you're all nice enough to send me in the code.
So I'm going to make this into a table with control T.
On the table tools, I will call it Data. All right, so we're going to start here.
On the data tab, From Table and Range, we're going to group by the name column.
Transform, Group By. I've used Group By before to total things.
So the new column name is going to be called AllRows, and the operation, check this out, get all rows.
What this does is it creates a tiny little table for each person, all right?
And now we have to write the M code to add that extra line in. So on the Home tab, we go to the Advanced Editor.
It's funny, I hate when the VBA editor does...
It selects something and then does something to the selection, and I always want to record some code and then go in and see that code, I want to fix it.
Well, one of the frustrating things with the Power Query editor is they use these variable names with spaces in the middle, which mean that you have to put quotes around it with a hashtag in front.
So I guess it's a little OCD here, but I need to fix these, just because it drives me crazy to know that there's variable names back there with spaces. Who would ever do that?
All right, so we go to the last line of code that we have.
I type a comma there, which says that we're adding another line of code, and then I'll paste in this line. So AddBlankRow is the new step.
Table.TransformColumns to the last step, GroupedRows.
We're going to expand the all rows. For each table, we're going to insert.
That little underscore is referring to the current table.
Table.RowCount. So what is that doing?
That's saying, "Hey, if there's five rows, they're numbered 0, 1, 2, 3, 4, and add a new row at position five," right? It's a cool bit of code.
And then for good measure, specify that each of those are null.
All right, so that creates a new step called AddBlankRow, and we'll just make sure that this kind of works when I go back to the Power Query editor.
Make sure I spell that AddBlankRow. There we go.
So we should be able to do Done.
And we now have this extra step that appeared in applied steps. From here, I don't need this column anymore.
We'll remove that column. And then we'll expand this.
I don't want to use the original column names as the prefix.
Choose Okay, and check it out.
They've sorted it, and it has the blank rows in between. Here's the close and load.
All right, this is great.
So from what I've read of Suat's book so far, he says that only about 50% of Power Query is actually available in the Power Query editor.
And learning when there is something that you can do outside of the Power Query editor, I guess will make you a superpower in Power Query.
Thanks to my friends Bill and Radoslaw and Geert and Rico and Suat for helping me there.
I realize I'm just an apprentice Power Query person here, realizing that there's M code that can be edited and still very uncomfortable when I have to go and edit it. But hey, give it a try.
It's not that bad if you're diving into M code for the first time.
All right. Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I was cleaning a fixed-width text file in Power Query.
And when I got to the end, they wanted a blank row after every group of names, and I just had to go out to Excel to do that.
But several people, Bill Szysz, Radoslaw Poprawski, Geert Delmulle, and Rico all said, "Now, hey, here's a code that you can do that".
So I turned to my friend, Suat Ozgur, who's writing this book, You Wouldn't Write Professional VBA Code Using the Macro Recorder, Would You?: A Programmer's Guide to M. So I'm going to do a hybrid approach here.
I'm going to try and do as much of this in the Power Query editor as I can, but the one line where we add those rows is going to have to be some M code, all right?
So we start out with data here.
My goal is to get all the Andy records together and then after Andy, a blank row.
Is that a good idea to do? No, right?
But the person who I ran into who had the file, that was how they needed to produce the data.
So I don't want to hear that we shouldn't have blank rows in our data.
It was a requirement, and you're all nice enough to send me in the code.
So I'm going to make this into a table with control T.
On the table tools, I will call it Data. All right, so we're going to start here.
On the data tab, From Table and Range, we're going to group by the name column.
Transform, Group By. I've used Group By before to total things.
So the new column name is going to be called AllRows, and the operation, check this out, get all rows.
What this does is it creates a tiny little table for each person, all right?
And now we have to write the M code to add that extra line in. So on the Home tab, we go to the Advanced Editor.
It's funny, I hate when the VBA editor does...
It selects something and then does something to the selection, and I always want to record some code and then go in and see that code, I want to fix it.
Well, one of the frustrating things with the Power Query editor is they use these variable names with spaces in the middle, which mean that you have to put quotes around it with a hashtag in front.
So I guess it's a little OCD here, but I need to fix these, just because it drives me crazy to know that there's variable names back there with spaces. Who would ever do that?
All right, so we go to the last line of code that we have.
I type a comma there, which says that we're adding another line of code, and then I'll paste in this line. So AddBlankRow is the new step.
Table.TransformColumns to the last step, GroupedRows.
We're going to expand the all rows. For each table, we're going to insert.
That little underscore is referring to the current table.
Table.RowCount. So what is that doing?
That's saying, "Hey, if there's five rows, they're numbered 0, 1, 2, 3, 4, and add a new row at position five," right? It's a cool bit of code.
And then for good measure, specify that each of those are null.
All right, so that creates a new step called AddBlankRow, and we'll just make sure that this kind of works when I go back to the Power Query editor.
Make sure I spell that AddBlankRow. There we go.
So we should be able to do Done.
And we now have this extra step that appeared in applied steps. From here, I don't need this column anymore.
We'll remove that column. And then we'll expand this.
I don't want to use the original column names as the prefix.
Choose Okay, and check it out.
They've sorted it, and it has the blank rows in between. Here's the close and load.
All right, this is great.
So from what I've read of Suat's book so far, he says that only about 50% of Power Query is actually available in the Power Query editor.
And learning when there is something that you can do outside of the Power Query editor, I guess will make you a superpower in Power Query.
Thanks to my friends Bill and Radoslaw and Geert and Rico and Suat for helping me there.
I realize I'm just an apprentice Power Query person here, realizing that there's M code that can be edited and still very uncomfortable when I have to go and edit it. But hey, give it a try.
It's not that bad if you're diving into M code for the first time.
All right. Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
