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

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

[@gnanpra3092]

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

[@AccessAnalytic]

You’re welcome. I appreciate you taking the time to let me know you found it useful

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

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

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

[@AccessAnalytic]

Thank you Andrés

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

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

[@andrewsinha2785]

Love the data wrangling tips! Thanks

[@AccessAnalytic]

Great! Thanks for letting me know Andrew

[@GrainneDuggan_Excel]

Love it. So many tips packed in a few minutes

[@AccessAnalytic]

Cheers Grainne 😀

[@joserochefort7778]

Like always your explanations are very clear !!!

[@AccessAnalytic]

You’re very welcome

[@tracylippiatt3238]

I love the way you think Wyn - great idea

[@AccessAnalytic]

Thanks Tracy 😀

[@larsbengtsson3216]

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

[@AccessAnalytic]

You’re welcome. I appreciate you taking the time to let me know you found it useful

[@rick_.]

I always learn something from your videos!

[@AccessAnalytic]

Glad they help Rick

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

[@mohamedrazaa585]

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

[@khemkas]

Incredibly useful. Thank you very much!

[@AccessAnalytic]

You’re welcome Shagun

[@kebincui]

Fabulous!👍🌹🌹Thanks Wyn for sharing

[@AccessAnalytic]

You’re welcome Kebin

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

[@ahmedkammoun9409]

Pretty usefull !and i LOVE your english accent !

[@AccessAnalytic]

Thanks, it's half Australian Half Welsh 😆

[@iliesboukhatem7803]

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

[@AccessAnalytic]

You’re welcome

[@MacroVisionInsight]

Amazing solution, thank you!

[@AccessAnalytic]

You’re welcome

[@malejandrahorvath]

This is great! Thank you Wyn!

[@AccessAnalytic]

Thanks Alejandra 😀. Good work on your new video too

[@malejandrahorvath]

@@AccessAnalytic Thank you so much!! 😊

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

[@DanielBula1964]

This is brilliant! Thank you.

[@AccessAnalytic]

Cheers Daniel

[@dandane3819]

This is so so clever!

[@AccessAnalytic]

Thanks Dan 😊

[@nadiadeshaies1715]

Wow! You are a gem!!!

[@AccessAnalytic]

Thanks Nadia ☺️

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

[@ChagoiSteve]

Fab just what I needed!

[@AccessAnalytic]

Great to know !

[@juanpablodelgadoguirola3758]

Thanks, nicely explained

[@AccessAnalytic]

You’re welcome Juan

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

[@martyc5674]

Very good- some useful tricks in there :)

[@AccessAnalytic]

Cheers Martin, thanks for letting me know

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

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

[@Scientificandresearch]

Very useful

[@AccessAnalytic]

You're welcome

[@luigibru8677]

Well explained

[@AccessAnalytic]

Thank you

[@IsaiahHuang]

very impressive!

[@AccessAnalytic]

Thanks Isaiah

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

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

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

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

[@gvinodnair]

Brilliant

[@AccessAnalytic]

Too kind Vinod

[@rrrraaaacccc80]

💯👍

[@AccessAnalytic]

😀

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