Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Insert Empty Rows (improvements?)

  1. #1
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    138
    Articles
    0
    Excel Version
    Office 365

    Insert Empty Rows (improvements?)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Borrowing some code from here Add blank row at each change in value

    I made a couple of adjustments allowing the ability add multiple rows. I also changed how the Column Types get reset.

    Code:
    let
        AddEmptyRows = (Tbl as table, HeadersToGroup as list, groupKind as number, optional NumberOfRows as nullable number) as table =>
        let 
            FirstRecord = Tbl{0},
            InsertCount = if NumberOfRows = null or NumberOfRows = 0 then 1 else  NumberOfRows,
    
    
            // create an empty record
            EmptyRecord = 
                let 
                    FieldNames = Record.FieldNames(FirstRecord),
                    EmptyValues = List.Repeat({""},Record.FieldCount(FirstRecord))
                in
                    Record.FromList(EmptyValues,FieldNames),
    
    
            // group table
            GrpTable = Table.Group(
                            Tbl, 
                            HeadersToGroup, 
                            {"Tbl", each _, type table}, 
                            groupKind ),
         
           // transform table column of grouped table adding Number Of Rows at the bottom
            TransformTblCol = Table.TransformColumns(
                                  GrpTable,
                                  {"Tbl", each Table.InsertRows(_, Table.RowCount(_), List.Repeat({EmptyRecord},InsertCount))}
                               ),
    
    
            // Select the tbl column and expand it
            ExpandColumns = Table.ExpandTableColumn(
                                Table.SelectColumns(
                                    TransformTblCol,
                                    {"Tbl"}
                                    ),
                            "Tbl",
                            Record.FieldNames(FirstRecord)
                            ),
            // Restore Column Types
            Result = Value.ReplaceType(ExpandColumns,Value.Type(Tbl))
    
    
        in
          Result
    in
        AddEmptyRows
    Given that the code is several years old, I was wondering if there are any improvements that could be made?
    Oh... by the way, YOU'RE WELCOME!

  2. #2
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    138
    Articles
    0
    Excel Version
    Office 365
    Here's an alternate version that eliminates the need to create an empty record, and appends a null table during the group, which eliminates the TransformColumns step as well.
    Not sure if it's better or not
    Code:
    let
        AddNullRows = (Tbl as table, HeadersToGroup as list, groupKind as number, optional NumberOfRows as nullable number) as table =>
        let 
            NoTable = if Table.IsEmpty(Tbl) then Tbl
            else
            let
                InsertCount = if NumberOfRows = null or NumberOfRows = 0 then 1 else  NumberOfRows,
    
    
                // group table and append null rows
                GrpTable = Table.Group(
                                Tbl, 
                                HeadersToGroup, 
                                {"Tbl", each Table.Combine({_, #table(List.FirstN(Table.ColumnNames(_),1),List.Repeat({{null}},InsertCount))}), type table}, 
                                groupKind ),
            
                // Select the tbl column and expand it
                ExpandColumns = Table.ExpandTableColumn(
                                Table.SelectColumns(
                                    GrpTable,
                                    {"Tbl"}
                                    ),
                                "Tbl",
                                Table.ColumnNames(Tbl)
                                ),
                Result = Value.ReplaceType(ExpandColumns,Value.Type(Tbl))
    
    
            in
                Result
        in
            NoTable
    in
        AddNullRows
    Oh... by the way, YOU'RE WELCOME!

  3. #3
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    Hi Nick,

    for a long time I thought to myself, "who needs something like that?" But now I have an application where I could definitely use the blank lines.

    Unfortunately I'm probably too stupid to call the function from a query. With the reference you linked, I manage to get the function to run, but it outputs the lines of the query multiple times.

    Therefore, it would be nice if you could show with a code example how the function is called.

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    With a few simple steps I have now managed to insert the desired blank lines.
    Attached Files Attached Files

  5. #5
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    138
    Articles
    0
    Excel Version
    Office 365
    Hi pinarello -

    The functions aren't invoked as an Add Custom Column, but as their own step, such as this:

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        Change_Type = Table.TransformColumnTypes(Source,{{"Customer_ID", Int64.Type}, {"Department", type text}, {"PricePaid", Int64.Type}}),
        CustomFunction = InsertEmptyRow(Change_Type, {"Customer_ID"}, GroupKind.Local)
    in
        CustomFunction
    A great many of my Power Query solutions are in conjunction with macros. We used to use a third-party program called Monarch, but somewhere around version 12, they removed the COM interface. So we were stuck with V10.
    That is until I discovered what Power Query could do what Monarch did plus a whole lot more.

    So instead of the the macro/Monarch transforming our ERP data, I use Power Query to get it into the final format as close as possible, with data breaks and even subtotal headers (not the actual totals). This way the macro really just needs to format the data and put in the formulas.

    I joke to coworkers that I use Power Query to take dirty data and turn it into "clean" dirty data.
    Oh... by the way, YOU'RE WELCOME!

  6. #6
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    Thanks Nick!

    I had almost thought so, but I had somehow a thick board in front of the head (a German proverb: "Ein dickes Brett vor dem Kopf").

    During my long career as a software developer, the last 25 years on an SAP system, I have often experienced that for external evaluations data was needed in a different form than it was provided by self-developed programs of the SAP system. In the past, when we were still spared from the bureaucracy octopus, I often adapted the output formatting of SAP programs with very little effort, so that they could generate parameter-controlled, alternative layouts. Then, as the walls of bureaucracy became higher and higher, I often, with much more effort, brought the data into the required form with Excel. Mostly with formulas, because I didn't know VBA that well and it was also officially never my task to take care of these things. But since I felt sorry for the many users who knew even less how to best transform the data, I took care of it anyway.

    Power Query is a big step forward, even if I only do it as a hobby. My biggest PQ (hobby) project was the development of a fuzzy search for addresses. About ten years ago I had developed a very fast and, in my opinion, very good fuzzy search for customers, articles and suppliers with ABAP. All in all a bit more than 10.000 lines of code. For the solution with Power Query only a little more than 200 M-code steps were necessary.

  7. #7
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    138
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by pinarello View Post
    Thanks Nick!

    I had almost thought so, but I had somehow a thick board in front of the head (a German proverb: "Ein dickes Brett vor dem Kopf").

    During my long career as a software developer, the last 25 years on an SAP system, I have often experienced that for external evaluations data was needed in a different form than it was provided by self-developed programs of the SAP system. In the past, when we were still spared from the bureaucracy octopus, I often adapted the output formatting of SAP programs with very little effort, so that they could generate parameter-controlled, alternative layouts. Then, as the walls of bureaucracy became higher and higher, I often, with much more effort, brought the data into the required form with Excel. Mostly with formulas, because I didn't know VBA that well and it was also officially never my task to take care of these things. But since I felt sorry for the many users who knew even less how to best transform the data, I took care of it anyway.

    Power Query is a big step forward, even if I only do it as a hobby. My biggest PQ (hobby) project was the development of a fuzzy search for addresses. About ten years ago I had developed a very fast and, in my opinion, very good fuzzy search for customers, articles and suppliers with ABAP. All in all a bit more than 10.000 lines of code. For the solution with Power Query only a little more than 200 M-code steps were necessary.
    Wow! Awesome! it is amazing how much VBA code I no longer have to write due to Power Query.
    Oh... by the way, YOU'RE WELCOME!

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,023
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by pinarello View Post
    With a few simple steps I have now managed to insert the desired blank lines.

    Allowing errors to occur then replacing them perhaps?:
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        GroupedRows = Table.Group(Source, {"Customer_ID"}, {{"grp", each Table.InsertRows(_,Table.RowCount(_),{null})}}),
        Expandedgrp = Table.ExpandTableColumn(GroupedRows, "grp", {"Customer_ID", "Department", "PricePaid"}, {"Customer_ID.1", "Department", "PricePaid"}),
        ReplaceErrors = Table.ReplaceErrorValues(Expandedgrp, List.Zip({Table.ColumnNames(Expandedgrp),{null}}))
    in
        ReplaceErrors
    If you want more than one row added (say 2), change:
    Code:
    GroupedRows = Table.Group(Source, {"Customer_ID"}, {{"grp", each Table.InsertRows(_,Table.RowCount(_),{null})}}),
    to:
    Code:
    GroupedRows = Table.Group(Source, {"Customer_ID"}, {{"grp", each Table.InsertRows(_,Table.RowCount(_),List.Repeat({null},2))}}),

  9. #9
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    very nice and short method. Only as next step a error-replacment is necassary.

  10. #10
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    138
    Articles
    0
    Excel Version
    Office 365
    {smacks forehead} I forgot about Table.InsertRows()... I'll have to modify my functions!
    Oh... by the way, YOU'RE WELCOME!

Page 1 of 2 1 2 LastLast

Posting Permissions

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