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

DrDebit

Board Regular
Joined
May 20, 2013
Messages
87
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Here's one approach.
I started with data in this Excel Table named Table1:
NameAmount
Alpha188
Alpha272
Alpha151
Alpha268
Bravo294
Bravo108
Charlie246
Charlie163
Charlie260
Charlie203
Charlie110

<tbody>
</tbody>

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:
Group.NameGroup.AmountIndex
Alpha1880
Alpha2720
Alpha1510
Alpha2680
1
Bravo2942
Bravo1082
3
Charlie2464
Charlie1634
Charlie2604
Charlie2034
Charlie1104
5

<tbody>
</tbody>

Is that something you can work with?
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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
 

citizenbh

Board Regular
Joined
Sep 19, 2013
Messages
134
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"
 

citizenbh

Board Regular
Joined
Sep 19, 2013
Messages
134
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"
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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:

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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
 

DrDebit

Board Regular
Joined
May 20, 2013
Messages
87
Thank you so much, Ron.





Here's one approach.
I started with data in this Excel Table named Table1:
NameAmount
Alpha188
Alpha272
Alpha151
Alpha268
Bravo294
Bravo108
Charlie246
Charlie163
Charlie260
Charlie203
Charlie110

<tbody>
</tbody>

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:
Group.NameGroup.AmountIndex
Alpha1880
Alpha2720
Alpha1510
Alpha2680
1
Bravo2942
Bravo1082
3
Charlie2464
Charlie1634
Charlie2604
Charlie2034
Charlie1104
5

<tbody>
</tbody>

Is that something you can work with?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,589
Messages
5,445,375
Members
405,327
Latest member
Robweg

This Week's Hot Topics

Top