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

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

Fab just what I needed!

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

Wow! You are a gem!!!

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

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

Love it. So many tips packed in a few minutes

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

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

This is so so clever!

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

Love the data wrangling tips! Thanks

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

Like always your explanations are very clear !!!

Incredibly useful. Thank you very much!

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

I always learn something from your videos!

I love the way you think Wyn - great idea

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

Fabulous!👍🌹🌹Thanks Wyn for sharing

Very nice method. I like your 2nd method.

This is brilliant! Thank you.

This is great! Thank you Wyn!

Amazing solution, thank you!

Pretty usefull !and i LOVE your english accent !

Thanks, nicely explained

Very useful

Very good- some useful tricks in there :)

Well explained

very impressive!

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"

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.

Brilliant

💯👍

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 ?

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 !

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.

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 :

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?

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.

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

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)