Excel - Power Query Insert Blank Row After Each Group - Episode 2552

Microsoft Excel Tutorial: Add a blank row after each group in Power Query.

Welcome to another tutorial on Power Query! In this video, we will be learning how to insert a blank row after each group in our data. This is a common task that many of us encounter while working with data, and it can be quite frustrating to have to go back to Excel to add those blank rows. But fear not, because with the help of some amazing code shared by our viewers, we will be able to do this right in the Power Query editor.

A few weeks ago, in video 2359, I was cleaning a fixed-width text file in Power Query. And towards the end, I needed to add a blank row after every group of names. I had to resort to using Excel for this task, but thanks to the suggestions from our viewers - Bill Szysz, Radoslaw Poprawski, Geert Delmulle, and Rico - we now have a code that can help us achieve this in Power Query. I also reached out to my friend Suat Ozgur, who is currently writing a book on Power Query, for some guidance on this task.

In this tutorial, we will be using a hybrid approach. We will try to do as much as we can in the Power Query editor, but for the final step of adding the blank rows, we will have to use some M code. So let's get started! Our goal is to group all the records for each person together and then add a blank row after each group. Now, this may not be the best practice, but it was a requirement for the data that I was working with. So let's not debate on whether we should have blank rows in our data or not, and focus on how we can achieve this task.

First, we will convert our data into a table by using the shortcut Ctrl + T. We will name this table "Data" and then go to the Data tab and select "From Table and Range". Next, we will group our data by the name column using the Transform ＞ Group By option. This may seem familiar as we have used the Group By function before to total values. But this time, we will use it to get all the rows for each person in a separate table. This will create a new column called "AllRows" with a tiny table for each person.

Now comes the tricky part - adding the blank rows. We will have to write some M code for this, so let's go to the Advanced Editor. One thing that I find frustrating about the Power Query editor is the use of variable names with spaces in between. This means we have to use quotes and a hashtag to refer to these variables. So I like to fix these variable names before proceeding with the code. Once that's done, we will add a new line of code using the comma and paste in the code shared by our viewers. This will create a new step called "AddBlankRow" which we will then apply to our data.

After applying the new step, we can remove the "AllRows" column and expand the data using the original column names as prefixes. And voila! We now have our data with the blank rows inserted after each group. This may seem like a daunting task, especially for those who are new to M code, but trust me, it's not that bad. And as I've learned from my friend Suat's book, there's a lot more that we can do with M code outside of the Power Query editor. So don't be afraid to dive in and explore the possibilities.

I want to thank all of you for your support and for sharing your knowledge with me. It's always a pleasure to learn from our viewers and to share that knowledge with others. I hope this tutorial has been helpful, and I'll see you in the next one. Don't forget to subscribe to our channel for more Power Query tips and tricks. Until next time, this is Bill Jelen, signing off.

Several people commented with code to add a blank row after each group in Power Query. Thanks to @BillSzysz1 @ExcelInstructor @GeertDelmulle Rico S and Suat Ozgur for guiding me on how to add one line of M code to my query.

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:47) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
insert blank rows in excel power query

[Excelambda]

PQ is king!! For fun a lambda to solve it, deals with tables or arrays (headers or no headers):
ar: any array or table
[h]: headers argument, omitted when array has no headers, 1 if table or array with headers
=LAMBDA(ar, [h],
LET(
a, IF(h, DROP(ar, 1), ar),
c, SEQUENCE(, COLUMNS(a)),
s, SORT(a), t, TAKE(s, , 1), u, UNIQUE(t),
r, REDUCE("", u,
LAMBDA(v, i,
LET(
x, XMATCH(i, t), y, XMATCH(i, t, , -1),
VSTACK(v, INDEX(s, SEQUENCE(y - x + 1, , x), c), "")))),
d, DROP(DROP(IFNA(r, ""), 1), -1),
IF(h, VSTACK(TAKE(ar, 1), d), d)
)
)

[GeertDelmulle]

OK, second attempt (the YT police jettisoned my first comment):
Hey Mr. Excel, here's a little improvement on your query:
In your GroupBy step (Table.Group) just replace "each _" in the aggregation line by: "each Table.Combine({_, #table({}, {{}})})".
That way you don't need to do the extra step of transforming a column to add the blank line.
In PQ-M you can always amend the formulas that are generated by the interface.
PS: thanks for mentioning me, really appreciate it.🙂

[peltiertech]

Hey, pretty cool. I was looking for a Dynamic Array approach because mine was ugly. But maybe the client is fine with using Power Query.

[iankr]

Many thanks, Bill. I've searched online for that Suat Ozgur book you advertise there, but I can't find it. Do you know where it's available? Been looking for a book on M that goes being the ribbon UI.

[roywilson]

There is always a new trick to learn, I have found you learn them when you need to do something specific. Thank heaven for Youtube creators who nurse you through the steps you need to take, can save a lot of time flicking back and forth through the documentation.

[mattschoular]

We were all apprentices at one point.... I am still at that point.... Thanks Mr.Excel

[Aahzmadius]

Can I pre-order Suat's book? Can't wait to get my hands on it!