Thanks Thanks:  0
Results 1 to 10 of 10

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

  1. #1
    New Member
    Join Date
    May 2013
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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


    in power query, add a blank row with a change in value
    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.

  2. #2
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: in power query, add a blank row with a change in value

    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?
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  3. #3
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,850
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: in power query, add a blank row with a change in value

    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

  4. #4
    Board Regular
    Join Date
    Sep 2013
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: in power query, add a blank row with a change in value

    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"

  5. #5
    Board Regular
    Join Date
    Sep 2013
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: in power query, add a blank row with a change in value

    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"

  6. #6
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: in power query, add a blank row with a change in value

    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
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  7. #7
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,850
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: in power query, add a blank row with a change in value

    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 by VBA Geek; Jul 22nd, 2018 at 02:21 PM.

  8. #8
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: in power query, add a blank row with a change in value

    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
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  9. #9
    New Member
    Join Date
    May 2013
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: in power query, add a blank row with a change in value

    Thank you so much, Ron.





    Quote Originally Posted by Ron Coderre View Post
    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?

  10. #10
    New Member
    Join Date
    May 2013
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: in power query, add a blank row with a change in value

    Thank you, too!

    David

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •