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
/ wynhopkins
Twitter
/ wynhopkins
Access Analytic Training
accessanalytic.com.au/training

[@mandypaulissen]

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

[@AccessAnalytic]

Glad to be of nerdy help 😄

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

[@AccessAnalytic]

Thank you Andrés

[@ChagoiSteve]

Fab just what I needed!

[@AccessAnalytic]

Great to know !

[@pascalpelzer6887]

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!!

[@AccessAnalytic]

Thanks for letting me know. Glad I could help

[@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

[@AccessAnalytic]

Fantastic ! Glad to help

[@GrainneDuggan_Excel]

Love it. So many tips packed in a few minutes

[@AccessAnalytic]

Cheers Grainne 😀

[@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 :)

[@AccessAnalytic]

We’re all learning Azzam, glad to help, thanks for taking the time to let me know it’s useful.

[@nadiadeshaies1715]

Wow! You are a gem!!!

[@AccessAnalytic]

Thanks Nadia ☺️

[@mohamedrazaa585]

WYN, you won our hearts ♥️♥️♥️

[@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!!

[@AccessAnalytic]

Excellent to know Iván 👏🏼

[@andrewsinha2785]

Love the data wrangling tips! Thanks

[@AccessAnalytic]

Great! Thanks for letting me know Andrew

[@iliesboukhatem7803]

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

[@AccessAnalytic]

You’re welcome

[@khemkas]

Incredibly useful. Thank you very much!

[@AccessAnalytic]

You’re welcome Shagun

[@kebincui]

Fabulous!👍🌹🌹Thanks Wyn for sharing

[@AccessAnalytic]

You’re welcome Kebin

[@tracylippiatt3238]

I love the way you think Wyn - great idea

[@AccessAnalytic]

Thanks Tracy 😀

[@wayneedmondson1065]

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

[@AccessAnalytic]

Cheers Wayne. Always more than 1 way to tackle these things

[@joserochefort7778]

Like always your explanations are very clear !!!

[@AccessAnalytic]

You’re very welcome

[@rick_.]

I always learn something from your videos!

[@AccessAnalytic]

Glad they help Rick

[@dandane3819]

This is so so clever!

[@AccessAnalytic]

Thanks Dan 😊

[@X1AbuBadr1X]

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

[@AccessAnalytic]

Glad to help you along your learning journey

[@FTSociety]

Amazing solution, thank you!

[@AccessAnalytic]

You’re welcome

[@Bhavik_Khatri]

Very nice method. I like your 2nd method.

[@AccessAnalytic]

Thanks, I'll be doing a video on a 3rd method several people submitted

[@Bhavik_Khatri]

@@AccessAnalytic Would like to see the third method too.

[@AccessAnalytic]

@@Bhavik_Khatri just published it

[@malejandrahorvath]

This is great! Thank you Wyn!

[@AccessAnalytic]

Thanks Alejandra 😀. Good work on your new video too

[@malejandrahorvath]

@@AccessAnalytic Thank you so much!! 😊

[@DanielBula1964]

This is brilliant! Thank you.

[@AccessAnalytic]

Cheers Daniel

[@juanpablodelgadoguirola3758]

Thanks, nicely explained

[@AccessAnalytic]

You’re welcome Juan

[@ahmedkammoun9409]

Pretty usefull !and i LOVE your english accent !

[@AccessAnalytic]

Thanks, it's half Australian Half Welsh 😆

[@Scientificandresearch]

Very useful

[@AccessAnalytic]

You're welcome

[@martyc5674]

Very good- some useful tricks in there :)

[@AccessAnalytic]

Cheers Martin, thanks for letting me know

[@IsaiahHuang]

very impressive!

[@AccessAnalytic]

Thanks Isaiah

[@luigibru8677]

Well explained

[@AccessAnalytic]

Thank you

[@IsaiahHuang]

We can also add an index column in the beginning as the "Journal" column in the video and remove it at the end. In this way, we won't hardcode any header names. Here is the M code. Thanks again for the inspiring video. let Source = Excel.CurrentWorkbook(){[Name="example"]}[Content], #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), OriginalData = #"Added Index", #"Removed Other Columns" = Table.SelectColumns(OriginalData,{"Index"}), #"Appended Query" = Table.Combine({#"Removed Other Columns", OriginalData}), #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}), #"Removed Top Rows" = Table.Skip(#"Removed Columns",1) in #"Removed Top Rows"

[@AccessAnalytic]

Thanks Isaiah, that's a nice solution if we want a blank row after every single entry.

[@IsaiahHuang]

Yes, I noticed that right after sending out the post and don't know how to withdraw the post 😆. Sorry for my ignorance. 😅 Again, I have seen how to insert blank rows before, but your method is simplest and with least hardcoding. Nice work! Thank you for sharing the idea.

[@AccessAnalytic]

@@IsaiahHuang - all suggestions are welcome :). Thanks for following the channel

[@asjones987]

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.

[@AccessAnalytic]

Good one Alan

[@tlee7028]

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 !

[@AccessAnalytic]

Difficult to explain without screen but I'm thinking add an INDEX Column, create a reference step that then picks the Journal column and the index column. Remove duplicates on the Journal column. Merge back with the query prior to the reference step and expand. That probably makes no sense! 😆

[@gvinodnair]

Brilliant

[@AccessAnalytic]

Too kind Vinod

[@9699975917]

Thanks! Could you calculate turn around time where after 3 pm it is consider as next day if it is weekend or Holiday then we have to exclude those days as well.

[@AccessAnalytic]

Yes that sort of thing is possible using Custom columns containing “if” functions

[@tonybarker4240]

Thanks for this tutorial - can you change it to add the blank row above each change and use the Journal number in the added row (so that it looks like a sub header) do you have a tutorial on that please ?

[@AccessAnalytic]

There’s probably a way using the opposite sort order to get the row at the top. Then some form of Add Column - Conditional column to get the label

[@rrrraaaacccc80]

💯👍

[@AccessAnalytic]

😀

[@nickbrown341]

How would you add multiple rows for each change in value? For example, I have a table of departments and project codes and I wanted to add a few new project codes for each department.

[@AccessAnalytic]

Not sure I understand the use case, but to add 2 rows where I added one you could in theory do an extra combine of the BlankRowHelper step

[@MrJohnreggie]

Thank you so much for the explaining ,,,,, Ok...I got it ....But now I would like to put 4 row between ,, how I can do that :

[@AccessAnalytic]

I’m not clear on what you mean sorry

[@MrJohnreggie]

great....and how can I add 4 row instead one between repited dates

[@AccessAnalytic]

Add a custom column to the Helper rows part containing ={1..4} then expand to rows

[@adrianchuzzz]

Great video. I followed every step and I was able to get my query with the blank rows between sets of data. However, when I loaded the query to my sheet, it shows the blank rows at the bottom. Do you have any idea what can the problem be?

[@AccessAnalytic]

Is there a sort on one of your table columns? Click On the excel table, go to data and click the clear filters icon then refresh

[@adrianchuzzz]

@@AccessAnalytic Thanks for your reply. There isn't any sorting, I even tried removing the filters.

[@AccessAnalytic]

Very odd - add an index column to your last power query step and then load that to see what happens

[@michaelthwaite3282]

Insert Blank Row for each change in a value. How about . . . Alt+F11 (opens VBA) - Insert Module - paste the following: Sub InsertRowsAtValueChange() 'Update 20140716 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Application.ScreenUpdating = False For i = WorkRng.Rows.Count To 2 Step -1 If WorkRng.Cells(i, 1).Value WorkRng.Cells(i - 1, 1).Value Then WorkRng.Cells(i, 1).EntireRow.Insert End If Next Application.ScreenUpdating = True End Sub Run macro highlighting the column where the data changes (eg Date or Time)

[@AccessAnalytic]

Yep a huge amount of Power Query can be replicated in VBA. Cheers Michael.