in power query, add a blank row with a change in value

D

DrDebit

Board Regular
Joined
May 20, 2013
Messages
123
Office Version
  1. 365
Platform
  1. Windows
I have a table of transactions with sales people that is cleaned and sorted so all transactions for a sales person are together in column A. In PQ, I would like to insert a blank row after each change in agent....similar to the subtotal function in Excel, except I would like to add a blank row to improve readability. Is it possible? Thank you so much in advance.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Here's one approach.
I started with data in this Excel Table named Table1:
[TABLE="width: 128"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]188[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]272[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]151[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]268[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD="align: right"]294[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD="align: right"]108[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]246[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]163[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]260[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]203[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]110[/TD]
[/TR]
</tbody>[/TABLE]

I referenced that table in Power Query....these are the annotated steps in the query:
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Group", each _, type table}}),

    // Start creating a list of names to intersperse with the existing names    
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Group"}),

    // Add a column containing the original name with "_Blank" appended
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each [Name] & "_Blank"),

    // Remove the original Name column
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Name"}),

    // Rename the new column to match the original data
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Name"}}),

    /* Append the new names to the previous data
       For this step I appended the "Renamed Columns" step to itself
       then I edited the step in the formula bar to use the "Grouped Rows" step
    */
    #"Appended Query" = Table.Combine({#"Grouped Rows", #"Renamed Columns"}),
    // Sort the records to place each new name below its parent
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Name", Order.Ascending}}),

    // Add an index to keep the grouped records in that order
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),

    // Remove the original "Name" column (it will be replaced in the next step)
    #"Removed Columns2" = Table.RemoveColumns(#"Added Index",{"Name"}),

    // Expand the new column
    #"Expanded Group" = Table.ExpandTableColumn(#"Removed Columns2", "Group", {"Name", "Amount"}, {"Group.Name", "Group.Amount"})
in
    #"Expanded Group"

If you load the results to a table...this is what you get:
[TABLE="width: 253"]
<tbody>[TR]
[TD]Group.Name[/TD]
[TD]Group.Amount[/TD]
[TD]Index[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]188[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]272[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]268[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD="align: right"]294[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]246[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

Is that something you can work with?
 
Upvote 0
Another way:

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],


    fn = (T as table) as table => 
            Table.InsertRows(T, 
                            Table.RowCount(T), 
                            {Record.TransformFields(T{0}, 
                                                    List.Zip({
                                                         Record.FieldNames(T{0}), 
                                                         List.Repeat({(_)=> null}, 
                                                                     Record.FieldCount(T{0}))
                                                        })
                                                   )}
                            ),


    Grp  = Table.Group(Source, {"Name"}, {{"Tbl", each _, type table}}, GroupKind.Global),


    TransformCol = Table.TransformColumns(Table.SelectColumns(Grp, {"Tbl"}), {{"Tbl", fn}}),


    FinalResult = Table.ExpandTableColumn(TransformCol, "Tbl", Table.ColumnNames(TransformCol{0}[Tbl]))
in
    FinalResult
 
Upvote 0
And another variant:

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"AllData", each _, type table}}),
    #"Inserted Row" = Table.TransformColumns(#"Grouped Rows",{{"AllData", each Table.InsertRows(_,0,{[Name = "", Amount = ""]})}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Inserted Row", "AllData", {"Name", "Amount"}, {"AllData.Name", "AllData.Amount"})
in
    #"Expanded AllData"
 
Upvote 0
Still a bit with totals per group:

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"AllData", each _, type table}}),
    #"Inserted Row" = Table.TransformColumns(#"Grouped Rows",{"AllData", each Table.InsertRows(_,0,{[Name = "Total: " & _[Name]{0}, Amount = List.Sum([Amount])]})}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Inserted Row", "AllData", {"Name", "Amount"}, {"AllData.Name", "AllData.Amount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded AllData",{"Name"})
in
    #"Removed Columns"
 
Upvote 0
VBA Geek

- I really didn't like that my inelegant, sloppily built, (insert other insults here) code was essentially single-use and did not accomodate changes to columns structure (apart from the Name column)

- I really liked the embedded function you put in your post. (but..I thought it was a bit arcane and difficult to follow)

So...Taking nothing away from your powerful solution...I reworked it to make the steps easier to follow. I hope you don't mind.

Here's my overhaul to your approach:
Using this sample data in an Excel Table named Table1:
Code: 
Name     Mth     Amount
Alpha     Jan     188
Alpha     Feb     272
Alpha     Mar     151
Alpha     Apr     268
Bravo     May     294
Bravo     Jun     108
Charlie   Jul     246
Charlie   Aug     163
Charlie   Sep     260
Charlie   Oct     203
Charlie   Nov     110

This Power Query code inserts a blank row between each group of Names:
QueryName: InsBlankRowBtwnGrps
QueryCode:
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    fnInsertNullRow = (TblRef as table) as table =>
        let     
            InsertionPoint  = Table.RowCount(TblRef),
            ColHdgs         = Record.FieldNames(TblRef{0}),
            ColCount        = Record.FieldCount(TblRef{0}),
            NewRowVals      = List.Repeat({null}, ColCount),
            NewRowToAdd     = Record.FromList(NewRowVals,ColHdgs),

            TblWithBlankRow = Table.InsertRows(TblRef, 
                                InsertionPoint, 
                                {NewRowToAdd})
            in
            TblWithBlankRow,

    Grp  = Table.Group(Source, {"Name"}, {{"Tbl", each _, type table}}, GroupKind.Global),

    TransformCol = Table.TransformColumns(Table.SelectColumns(Grp, {"Tbl"}), {{"Tbl", fnInsertNullRow}}),

    FinalResult = Table.ExpandTableColumn(TransformCol, "Tbl", Table.ColumnNames(TransformCol{0}[Tbl]))

in
    FinalResult

This is the end result:
Code: 
Name     Mth     Amount
Alpha     Jan     188
Alpha     Feb     272
Alpha     Mar     151
Alpha     Apr     268

Bravo     May     294
Bravo     Jun     108

Charlie   Jul     246
Charlie   Aug     163
Charlie   Sep     260
Charlie   Oct     203
Charlie   Nov     110
 
Upvote 0
Hi Ron,

after looking at it again, I do agree with you that in terms of performance (and transparency) it does need an improvement.

Actually in my previous post, the bit which was part of the function (which represents the second argument passed to the Table.InsertRows function)


Code: 
                            {Record.TransformFields(T{0}, 
                                                    List.Zip({
                                                         Record.FieldNames(T{0}), 
                                                         List.Repeat({(_)=> null}, 
                                                                     Record.FieldCount(T{0}))
                                                        })
                                                   )}

could be taken out completely of the function since it would essentially be always a list containing one single record whose fields are all to be blank. This would save us from asking PQ to recalculate it for each row when the Table.TransformColumns is called.

Thus, also in your clearer version, I would take the below bit out of the function:

Code: 
            InsertionPoint  = Table.RowCount(TblRef),
            ColHdgs         = Record.FieldNames(TblRef{0}),
            ColCount        = Record.FieldCount(TblRef{0}),
            NewRowVals      = List.Repeat({null}, ColCount),
            NewRowToAdd     = Record.FromList(NewRowVals,ColHdgs)


and do something like (untested and potentially buggy):



Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

            InsertionPoint  = Table.RowCount(Source),
            ColHdgs         = Record.FieldNames(Source {0}),
            ColCount        = Record.FieldCount(Source {0}),
            NewRowVals      = List.Repeat({null}, ColCount),
            NewRowToAdd     = Record.FromList(NewRowVals,ColHdgs),





    fnInsertNullRow = (TblRef as table) as table =>
        let     


            TblWithBlankRow = Table.InsertRows(TblRef, 
                                InsertionPoint, 
                                {NewRowToAdd})
            in
            TblWithBlankRow,


    Grp  = Table.Group(Source, {"Name"}, {{"Tbl", each _, type table}}, GroupKind.Global),


    TransformCol = Table.TransformColumns(Table.SelectColumns(Grp, {"Tbl"}), {{"Tbl", fnInsertNullRow}}),


    FinalResult = Table.ExpandTableColumn(TransformCol, "Tbl", Table.ColumnNames(TransformCol{0}[Tbl]))


in
    FinalResult

PS. The final touch could be to turn the whole thing into function with 2 paramenters. Paramter 1: the table on which we want to add an empty row, Parameter2: a list (or single text value) representing the column name(s) used to perform the GroupBy when a new empty row is to be added
 
Last edited:
Upvote 0
Pretty good for not testing!
The InsertionPoint has to be associated with the group rows:
(That's the only change necessary)

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

            ColHdgs         = Record.FieldNames(Source{0}),
            ColCount        = Record.FieldCount(Source{0}),
            NewRowVals      = List.Repeat({null}, ColCount),
            NewRowToAdd     = Record.FromList(NewRowVals,ColHdgs),

    fnInsertNullRow = (TblRef as table) as table =>
        let     
            [B][COLOR=#0000ff]InsertionPoint  = Table.RowCount(TblRef),[/COLOR][/B]
            TblWithBlankRow = Table.InsertRows(TblRef, 
                                InsertionPoint, 
                                {NewRowToAdd})
            in
            TblWithBlankRow,

    Grp  = Table.Group(Source, {"Name"}, {{"Tbl", each _, type table}}, GroupKind.Global),

    TransformCol = Table.TransformColumns(Table.SelectColumns(Grp, {"Tbl"}), {{"Tbl", fnInsertNullRow}}),

    FinalResult = Table.ExpandTableColumn(TransformCol, "Tbl", Table.ColumnNames(TransformCol{0}[Tbl]))

in
    FinalResult
 
Upvote 0
Thank you so much, Ron.





Ron Coderre said:
Here's one approach.
I started with data in this Excel Table named Table1:
[TABLE="width: 128"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]188[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]272[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]151[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]268[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD="align: right"]294[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD="align: right"]108[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]246[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]163[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]260[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]203[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]110[/TD]
[/TR]
</tbody>[/TABLE]

I referenced that table in Power Query....these are the annotated steps in the query:
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Group", each _, type table}}),

    // Start creating a list of names to intersperse with the existing names    
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Group"}),

    // Add a column containing the original name with "_Blank" appended
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each [Name] & "_Blank"),

    // Remove the original Name column
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Name"}),

    // Rename the new column to match the original data
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Name"}}),

    /* Append the new names to the previous data
       For this step I appended the "Renamed Columns" step to itself
       then I edited the step in the formula bar to use the "Grouped Rows" step
    */
    #"Appended Query" = Table.Combine({#"Grouped Rows", #"Renamed Columns"}),
    // Sort the records to place each new name below its parent
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Name", Order.Ascending}}),

    // Add an index to keep the grouped records in that order
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),

    // Remove the original "Name" column (it will be replaced in the next step)
    #"Removed Columns2" = Table.RemoveColumns(#"Added Index",{"Name"}),

    // Expand the new column
    #"Expanded Group" = Table.ExpandTableColumn(#"Removed Columns2", "Group", {"Name", "Amount"}, {"Group.Name", "Group.Amount"})
in
    #"Expanded Group"

If you load the results to a table...this is what you get:
[TABLE="width: 253"]
<tbody>[TR]
[TD]Group.Name[/TD]
[TD]Group.Amount[/TD]
[TD]Index[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]188[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]272[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]268[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD="align: right"]294[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]246[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

Is that something you can work with?
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Quicken QIF files to Power Query
2
Replies
14
Views
1K
smozgur
smozgur
M
  • Question Question
Scraping table from site with multiple pages but same url
Replies
1
Views
128
JGordon11
JGordon11
RICH937
  • Question Question
What is the right way to remove blank rows in power query
Replies
5
Views
671
Matt Allington
M
B
  • Solved
Power Query - Add multiple blank columns
Replies
4
Views
2K
borkybork
B
E
  • Question Question
Accounts Receivable ledger via Power Query
Replies
1
Views
417
johnny51981
J

Forum statistics

Threads
1,223,699
Messages
6,173,903
Members
452,536
Latest member
Chiz511

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top