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

**DrDebit** · Jul 20th, 2018, 07:37 PM

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.

**Ron Coderre** · Jul 21st, 2018, 08:17 AM

Here's one approach.
I started with data in this Excel Table named Table1:

Name	Amount
Alpha	188
Alpha	272
Alpha	151
Alpha	268
Bravo	294
Bravo	108
Charlie	246
Charlie	163
Charlie	260
Charlie	203
Charlie	110

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.Name	Group.Amount	Index
Alpha	188	0
Alpha	272	0
Alpha	151	0
Alpha	268	0
		1
Bravo	294	2
Bravo	108	2
		3
Charlie	246	4
Charlie	163	4
Charlie	260	4
Charlie	203	4
Charlie	110	4
		5

Is that something you can work with?

**VBA Geek** · Jul 21st, 2018, 01:32 PM

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** · Jul 22nd, 2018, 07:56 AM

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** · Jul 22nd, 2018, 11:55 AM

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** · Jul 22nd, 2018, 12:43 PM

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** · Jul 22nd, 2018, 01:18 PM

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

**Ron Coderre** · Jul 22nd, 2018, 01:59 PM

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     
            InsertionPoint  = Table.RowCount(TblRef),
            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** · Jul 26th, 2018, 04:58 PM

Thank you so much, Ron.

Originally Posted by Ron Coderre

Here's one approach.
I started with data in this Excel Table named Table1:

Name	Amount
Alpha	188
Alpha	272
Alpha	151
Alpha	268
Bravo	294
Bravo	108
Charlie	246
Charlie	163
Charlie	260
Charlie	203
Charlie	110

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.Name	Group.Amount	Index
Alpha	188	0
Alpha	272	0
Alpha	151	0
Alpha	268	0
		1
Bravo	294	2
Bravo	108	2
		3
Charlie	246	4
Charlie	163	4
Charlie	260	4
Charlie	203	4
Charlie	110	4
		5

Is that something you can work with?

**DrDebit** · Jul 26th, 2018, 04:59 PM

Thank you, too!

David

Thread: in power query, add a blank row with a change in value
Thanks: 0
Likes: 1

Post #5109465 (1)

Thread Tools

Display