Insert Blank Row for each change in a value using Power Query

Power Query in Excel can be used for so many purposes and converting data ready for upload into another system is just one.

In this scenario I show a couple of techniques to insert a row for each change in a group of items.

Did you know a Query can be appended with itself!?

00:00 Intro
00:38 Setting the Scenario
02:14 The Steps
03:56 Appending a query with itself and change a table name to a step name
05:15 Conditional Column
06:48 Alternative approach to adding a conditional column
08:53 Testing with extra data
09:30 Recommended extra elements - renaming and adding comments

Follow me on LinkedIn

Twitter

Access Analytic Training

[gnanpra]

You solved a big piece of my puzzle on my lengthy powerquery with this video! I cannot thank you enough! Godbless ya!!

[mandypaulissen]

It's insanely nerdy, but more importantly it's insanely It save a lot of precious life time!!

[IvanCortinas_ES]

A few years ago I barely played Power Query. Now, among other reasons, by watching your videos, I have become a fan of Power Query. Thank you Wyn!!

[XAbuBadrX]

You are amazing, im still a beginner in Power Query, but im learning so much from you

Outstanding! Excellent description of the thinking process to imagine the solution and progress to implementation.

[pascalpelzer]

Thanks a lot! The extra row and "append query" helped me in making a total row per "Journal" as it were. Very good video's!!

[midoriyaone]

Thank you so much! Huge thank you man! I performed this process at work manually and it took me a lot of time to do it, now everything is automated. Health and good luck, thanks again! :) You are a really cool professional

[GrainneDuggan_Excel]

Love it. So many tips packed in a few minutes

[tracylippiatt]

I love the way you think Wyn - great idea

[SustainaBIT]

that's the advanced techniques that we need more of!!!, I consider myself an advanced user of power query and I just learned from you something new. Thanks a ton :)

[larsbengtsson]

Thanks! You saved my day! (and a lot of days ahead!)

[joserochefort]

Like always your explanations are very clear !!!

[rick_.]

I always learn something from your videos!

[khemkas]

Incredibly useful. Thank you very much!

[wayneedmondson]

Hi Wyn. Awesome! Nice to understand two ways to solve it. Thanks for sharing :)) Thumbs up!!

[tlee]

Thank you for sharing! from your example, how would I remove repeated J1-23? I would only like to keep say... J1-23 and J1-24. thanks !

[ahmedkammoun]

Pretty usefull !and i LOVE your english accent !

[iliesboukhatem]

Thank you so much, very usefull and well explained :)

[Bhavik_Khatri]

Very nice method. I like your 2nd method.

[asjones]

nice this inspired me for one i i was looking at that needed 2 blank rows. For the blank row table i created a custom column with "1, 2" called it decimal. Then did a split column by delimiter but split to rows then converted to number and multiplied by .1. I then added a normal index and column. Using math I combined the index column + the decimal column. and got rows like 1.1, 1.2, 2.1, 2.2, 3.1, 3.2 then on my main table I created an index, Appended the new table and sorted. So the main Index was 1 with the blanks being 1.1, 1.2. then next group 2, 2.1, 2.2...
then it all sorted nice.

was surprised it worked so nicely.