Insert blank rows at each category in Power Query

Have you ever wanted to have a blank row after each category or subcategory, so you can visualize your data in a table?

In this video I will show you how to add a blank row for each group using power query in one step!

Chapters:
00:00 Intro & Use case
00:40 Group data by a column
01:26 Modify the group data M code to add the blank row
Done!


SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:


Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲


************





************


QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#CURBAL #SUBSCRIBE

[EricaDyson]

Got it! Thanks a lot, Ruth. Have a great Swedish summer (they are nice). You're an inspiration to all us lesser mortals!

[najdak]

Огромное СПАСИБО! Долго искал возможности решения составления отчёта не нарушая корпоративных стандартов. Вы сильно помогли, спасибо за труд!

[pete_kvist]

Goodness me. I've been trying to figure a workaround for this for months... and here you are. Genius.

[juanes]

3 years later and this video still helps and makes people :) . Thanks!!

[ralfwitte]

Easy and simple. Your explanation is just awesome. Thank you!

[YvesAustin]

Ruth, your videos are truly amazing and bring power query to a whole new dimension. I do not think there is anyone else out these who can compare. While you make it so easy to follow and just copy the syntax, it is far from evident! Did your mother speak M to you as a kid?!! Or are you a unicorn?!! One of the two must be true!! 🙏🙏

[jjjoannes]

I love you. I was trying to add subtotals rows to a table and here you are. What it misses is easy. Thanks!

[vida]

It is so amazing what Power Query can do. Actually, it is so amazing what Ruth can do with Power Query!

[justlive]

Quick, clear and useful ... 1+ for Ruth 👍👍

[MarceloAlmeida-hy]

Thanks for sharing this knowledge! You made it so simple! So Clean! Thanks a lot! 🙂

[theburningsolander]

Hi there, thanks so very much for this. Nicely explained. I am wondering, if you wanted more than one blank row at the end, how could you do that? Say you wanted 8 blank rows between category?

[fe_nicks]

Hi, Ruth! You are my Savior! That's exactly I've been looking for!.. Please advise if there is any option to insert blank rows BEFORE category, not AFTER? How do I change my query in this case? Thank you!!

[BretCarr]

That is a terrible lot of work to just insert a blank row! :) Excellent video. I had to watch your closing of the last lists and values carefully because mine didn't work straight out and that is with the new IDE interface! Sheesh!

[michellemar]

Wow- that could've saved me hours!!

[ilyasura]

Hello, Ruth! Thank you very much for your videos!
I have a question.
Can I add a "subtotal row" for each month that will add up all the sales values above.
🤞

[juliethramos]

Thank you so so so so much. I've been dealing with this for the last 3 days. I ended up doing manual Table.InsertRows and adding the offset to finally delivery a work, but that was something untenable in time since the # of rows will continously being changing in the source file. Just a quick question, what if I need to add the rows at the top of each table. Would a Table.FirstN work?
Once again...Thank you!!

[nitataylor]

Hi Ruth, Thank you for the simple explanation. I am trying to load the query into Excel so it keeps the blank rows but it doesn't. How can I do this. I have checked all the settings and can't see any option to change this. I have tried to load into the data model as a connection and as a table but still no blank rows. Any help would be appreciated.

[davidferrick]

Thanks. What if I wanted X amount of rows based on the number of rows of each month. For example January only has 2 rows, February has 1 row and March has 3 rows. I want a total of 5 rows for each so January would have 3 blank rows added, February would have 4 blank rows added and March would have 2 blank rows added so they all have 5 rows.

[projectdonimal]

Is there a way to control the number of blank rows that are added after each group? Say if I wanted 8 blank rows after each group instead of just 1?

[mariansdraila]

What if, instead o of a blank row, I want to add a headline row that contains the month name, so I can remove the month column?