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



## DrDebit (Jul 20, 2018)

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 21, 2018)

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

NameAmountAlpha188Alpha272Alpha151Alpha268Bravo294Bravo108Charlie246Charlie163Charlie260Charlie203Charlie110

<tbody>

</tbody>
I referenced that table in Power Query....these are the annotated steps in the query:

```
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.AmountIndexAlpha1880Alpha2720Alpha1510Alpha26801Bravo2942Bravo10823Charlie2464Charlie1634Charlie2604Charlie2034Charlie11045

<tbody>

</tbody>
Is that something you can work with?


----------



## VBA Geek (Jul 21, 2018)

Another way:


```
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 22, 2018)

And another variant:


```
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 22, 2018)

Still a bit with totals per group:


```
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 22, 2018)

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:

```
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*: 

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

```
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 22, 2018)

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)



```
{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: 


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




```
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 22, 2018)

Pretty good for not testing!
The InsertionPoint has to be associated with the group rows:
(That's the only change necessary)


```
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 (Jul 26, 2018)

Thank you so much, Ron.







Ron Coderre said:


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


----------



## DrDebit (Jul 26, 2018)

Thank you, too!

David


----------



## DrDebit (Jul 20, 2018)

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.


----------



## XL Pro (Sep 26, 2021)

I know this is 3 years later, but I believe this technique would have been relevant then as it is now.
Instead of trying to null all the field values, you can use a Table.Combine() and only null 1 field as the rest of the fields will null automatically.
So, the query would look like this:


```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    fnInsertNullRow = (TblRef as table) as table =>
        let     
            TblWithBlankRow =Table.Combine({TblRef, #table(List.FirstN(Table.ColumnNames(TblRef),1),{{null}})})
        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
```

Alternately, you could combined the blank row in the group statement, and wrap that in in the Table.SelectColumns() as such:


```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GrpInsert = Table.SelectColumns(Table.Group(Source, {"Name"}, {{"Tbl", each Table.Combine({_, #table(List.FirstN(Table.ColumnNames(_),1),{{null}})}), type table}}, GroupKind.Global),{"Tbl"}),
    ExpandeTbl = Table.ExpandTableColumn(GrpInsert, "Tbl", {"Name", "Amount"}, {"Name", "Amount"})
in
    ExpandeTbl
```


----------

