Insert Empty Rows (improvements?)

Nick Burns

Nick Burns

Member
Joined
May 24, 2017
Messages
162
Reaction score
0
Points
16
Excel Version(s)
Office 365
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?
 
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
 
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.
 
With a few simple steps I have now managed to insert the desired blank lines.
 

Attachments

  • Excel PQ - Insert empty lines.xlsx
    27.8 KB · Views: 6
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. :D
 
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.
 
pinarello said:
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.
Click to expand...
Wow! Awesome! it is amazing how much VBA code I no longer have to write due to Power Query.
 
pinarello said:
With a few simple steps I have now managed to insert the desired blank lines.
Click to expand...


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))}}),
 
very nice and short method. Only as next step a error-replacment is necassary.
 
{smacks forehead} I forgot about Table.InsertRows()... I'll have to modify my functions!
 
Looking at my first example I am using the Table.InsertRows. But because of the Error issue, I think that's why I came up with the alternate version. If you want to eliminate the Errors in the GroupBy step, it can be re-written as such:

Code: 
= Table.Group(PreviousStep, {"Customer_ID"}, {{"grp", each Table.InsertRows(_,
                 Table.RowCount(_),
                 {Record.TransformFields(_{0},
                   List.Zip({
                    Record.FieldNames(_{0}), 
                    List.Repeat({(_)=> null}, 
                     Record.FieldCount(_{0}))
                    })
                   )}
                  )}}
               )
A little more convoluted, but it works. I think I went with the Table.Combine() method as you only had to worry about 1 column matching and the others fill in with null automatically.
 
You must log in or register to reply here.
Back
Top