Insert Blank Row for each change in a value using Power Query
HTML-код
- Опубликовано: 5 авг 2024
- 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 
Хобби
Thanks! You saved my day! (and a lot of days ahead!)
You’re welcome. I appreciate you taking the time to let me know you found it useful
It's insanely nerdy, but more importantly it's insanely brilliant!!!!!! It save a lot of precious life time!!
Glad to be of nerdy help 😄
Outstanding! Excellent description of the thinking process to imagine the solution and progress to implementation.
Thank you Andrés
Love it. So many tips packed in a few minutes
Cheers Grainne 😀
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 :)
We’re all learning Azzam, glad to help, thanks for taking the time to let me know it’s useful.
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
Fantastic ! Glad to help
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!!
Thanks for letting me know. Glad I could help
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!!
Excellent to know Iván 👏🏼
Love the data wrangling tips! Thanks
Great! Thanks for letting me know Andrew
Fab just what I needed!
Great to know !
WYN, you won our hearts ♥️♥️♥️
Incredibly useful. Thank you very much!
You’re welcome Shagun
Like always your explanations are very clear !!!
You’re very welcome
Hi Wyn. Awesome! Nice to understand two ways to solve it. Thanks for sharing :)) Thumbs up!!
Cheers Wayne. Always more than 1 way to tackle these things
Fabulous!👍🌹🌹Thanks Wyn for sharing
You’re welcome Kebin
I always learn something from your videos!
Glad they help Rick
I love the way you think Wyn - great idea
Thanks Tracy 😀
This is brilliant! Thank you.
Cheers Daniel
Thank you so much, very usefull and well explained :)
You’re welcome
You are amazing , im still a beginner in Power Query , but im learning so much from you
Glad to help you along your learning journey
Wow! You are a gem!!!
Thanks Nadia ☺️
This is great! Thank you Wyn!
Thanks Alejandra 😀. Good work on your new video too
@@AccessAnalytic Thank you so much!! 😊
Amazing solution, thank you!
You’re welcome
This is so so clever!
Thanks Dan 😊
Thanks, nicely explained
You’re welcome Juan
Very nice method. I like your 2nd method.
Thanks, I'll be doing a video on a 3rd method several people submitted
@@AccessAnalytic Would like to see the third method too.
@@Bhavik_Khatri just published it
Pretty usefull !and i LOVE your english accent !
Thanks, it's half Australian Half Welsh 😆
Very good- some useful tricks in there :)
Cheers Martin, thanks for letting me know
Well explained
Thank you
very impressive!
Thanks Isaiah
Very useful
You're welcome
Brilliant
Too kind Vinod
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"
Thanks Isaiah, that's a nice solution if we want a blank row after every single entry.
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.
@@IsaiahHuang - all suggestions are welcome :). Thanks for following the channel
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.
Good one Alan
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 !
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! 😆
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 ?
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
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.
Yes that sort of thing is possible using Custom columns containing “if” functions
💯👍
😀
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 :
I’m not clear on what you mean sorry
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.
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
great....and how can I add 4 row instead one between repited dates
Add a custom column to the Helper rows part containing ={1..4} then expand to rows
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?
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
@@AccessAnalytic Thanks for your reply. There isn't any sorting, I even tried removing the filters.
Very odd - add an index column to your last power query step and then load that to see what happens
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)
Yep a huge amount of Power Query can be replicated in VBA. Cheers Michael.