Livio/ July 27, 2018/ Power BI, Power Query/ 27 comments

Power Query – Add blank row at each change in value

This is a follow-up post to a question asked on Mr Excel forum. The OP wanted to use Power Query to add an empty row in a table every time a value in a certain column changed. I thought it would be interesting to turn the solution into a general function anyone can use. Add Blank Row Power Query

Add Blank Row Power Query

The function accepts three arguments:

  • Tbl: represents the table on which the empty rows are to be added
  • HeadersToGroup: this is a list containing the headers names of Tbl you want to use to add an empty row. For instance, if your table has a header called CustomerID, you would use {“CustomerID”} in order to insert a row at each change in customer ID. If you wanted to add a blank row at each change of CustomerID and Department, you would use {“CustomerID”, “Department”}.
  • gorupKind: This is an interesting argument. You would use GroupKind.Local if you wanted to insert a blank row at each change of HeadersToGroup, or GroupKind.Global if you wanted to first group by HeadersToGroup and then insert a blank row. If you have the same CustomerID not in adjacent rows, GroupKind.Global would put them all in adjacent rows and then insert a blank row

Function M Code:

 

Starting from a simple table like the one below, I show the different results you can achieve:

Power Query Add Blank Row

 

InsertBlankRow(ChangedType, {“CustomerID”}, GroupKind.Global):

Power Query Add Blank Row

 

InsertBlankRow(ChangedType, {“CustomerID”}, GroupKind.Local)

POwer Query Add Blank Row

 

InsertBlankRow(ChangedType, {“CustomerID”,”Department”}, GroupKind.Global):

Power Query Add Blank Row

Must read books if you want to become a Power Query guru:

 

27 Comments

  1. Roman

    Wow, thanks for sharing. However, I get this error:

    An error occurred in the ‘Add Empty Row’ query. Expression.Error: The column ‘A’ of the table wasn’t found.
    Details:
    A

    Any ideas what I’m doing wrong? Thank you.

    1. Livio author

      Hi Roman, can you show me your Power Query code?

      Livio

  2. Laurent

    Hi Livio,
    Thanks for sharing, it’s very useful! However I would like to know if it’s possible to prevent errors in case of the source table is empty ? I’ve got the message :
    “An error occurred in the “AddEmptyRows” query. Expression.Error: There were not enough elements in the enumeration to complete the operation.
    Details:
         Table”

    Any idea PLEASE? 🙂

    Thanks in advance, Laurent

    1. Livio author

      Hi Laurent, thanks for the feedback! 🙂
      You could add a test at the beginning of the function such as:

      let
      AddEmptyRows = (Tbl as table, HeadersToGroup as list, groupKind as number) as table =>
      let
      Result =
      // test if Table is Empty
      if Table.IsEmpty(Tbl) then
      Tbl
      else
      let
      FirstRecord = Tbl{0},

      // 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 1 empty row at the bottom
      TransformTblCol = Table.TransformColumns(
      GrpTable,
      {“Tbl”, each Table.InsertRows(_, Table.RowCount(_), {EmptyRecord})}
      ),

      // Select the tbl column and expand it
      ExpandColumns = Table.ExpandTableColumn(
      Table.SelectColumns(
      TransformTblCol,
      {“Tbl”}
      ),
      “Tbl”,
      Record.FieldNames(FirstRecord)
      ),

      // build a list containing the header name and the Type of each column of Source
      ColTypes = List.Accumulate(
      Record.FieldNames(FirstRecord),
      {},
      (state, current) => state & {{current, Value.Type(Record.Field(FirstRecord, current))}}
      )

      in
      Table.TransformColumnTypes(ExpandColumns ,ColTypes)
      in
      Result
      in
      AddEmptyRows

  3. JasonPM

    Hi, Lizo.

    I’m trying to test this out in some of my work (As this is brilliant) and I’m a bit clueless tbh.

    I try to invoke the function in Power Query Excel 2016 and it is asking for a number value in the groupKind box. I select tbl as Table3, HeadersToGroup as Query:Table3 & Column: Department.

    groupKind then asks for a number value. If I just put 1 in there I get this error message:

    “An error occurred in the ‘Blankrows’ query. Expression.Error: The column ‘TOY’ of the table wasn’t found.
    Details:
    TOY”

    Thanks for any help in advance! 🙂

    J

    1. Livio author

      Hi Jason!
      In the groupKind box you can input either 0 (for GroupKind.Local) or 1 (for GroupKind.Global).
      Judging by the error message you are showing, I would say there’s a missing column (named TOY in Table3).
      Remember that Power Query is case sensitive. Do you have this column in your table?

  4. Attila

    Hi,

    This is excellent, this is exactly what I need. Still, I have an error message:

    An error occurred in the ‘AddEmptyRows’ query. Expression.Error: The column ‘ED1130’ of the table wasn’t found.
    Details:
    ED1130

    ED1130 is the first record in the selected column, not the header. Please advise me what I need to change.

    thank you

    1. Livio author

      Hi Attila! The function accepts the header names, do you not have header names? Why did you supply the value of the first record?

  5. Attila

    Hi Livio!

    I have header names, I supplied it, still it shows the error column ED1130 was not found. Of course it is not found, since its header name is ATM ID, and it was supplied.

    thank you

  6. Dave

    Similar error/s for me. Either “Column “50312” not found” or:

    “An error occurred in the ‘Query1’ query. Expression.Error: We cannot convert the value 50312 to type Text.
    Details:
    Value=50312
    Type=Type ”

    “50312” being the 1st Data value of the column.

    1. Livio author

      Hi Dave, you have to provide the name of the header and not the value of the first row in the column.
      Why did you provide the first value of the column?

  7. Jocelyn

    I’m not sure if this would be the right code to use or not. I am looking to insert rows to do a couple of different functions
    1) Add a row at the top of the table (Above the headers), then merge and centre – (If this isn’t possible, I know I can achieve the required look with the Headers & Footers features of Excel)
    2) Add rows before each different date
    3) Separate Date & Time (I know how to do this)
    4) Change date to DDDD, MMMM, DD format (I know how to do this)
    5) Change time to AM/PM Format (I know how to do this)
    3) If there is nothing booked for that date, I would like a row added and “No Events Booked” be put into the “Venues Cell”

    Here is an example of the contents of a my table.
    My headers are”
    “Venue”, “Date-Start Time”, “End Time” “# Confirmed”, “Title”, “Room Setup”
    “Rm 1”, “14/09/2019 10:00” “14/09/2019 20:00” “20” “Meeting A” “Computer”
    “House”, “14/09/2019 11:00” “14/09/2019 16:00” “0” “Tours”, “Tour Setup”
    “House”, “16/09/2019 8:00” “16/09/2019 16:00” “1” “Course”, “N/A”

    What I need it to look like
    Daily Events, September 13-16, 2019 (This would be a new Row)
    Saturday September 14 (this would be a new row)
    “Rm 1” “10:00 AM” “8:00 PM” “20” “Meeting A” “Computer”
    “House” “11:00 AM” “4:00 PM” “0” “Tours” “Tour Setup”
    Sunday October 15 (This would be a new row)
    No Events Booked (This would be a new Row)
    Monday October 16 (This would be a new Row)
    “House” “8:00 PM” “4:00 PM” “1” “Course” “N/A”

    Is this possible to do in Power Query, or should I just record a Macro and do it that way. There are potentially 6 different people (none of which are very computer savvy) which will need to prepare this report on a daily basis, so I’m trying to make it as simple / streamlines & fast as possible

    1. Livio author

      Hello Jocelyn, step 2 can be accomplished by the code I posted, a single blank row is will be inserted at each change in ‘Date’

  8. Alex

    Hi Livio,

    thanks a lot for this post. It helped me solve a problem quickly.

    I experienced the same “missing column” error mentioned here before. The reason for this error seems to be differences in the M code for the second argument that the functions generates on my side (2) compared to your example code (1):

    (1) InsertBlankRow(ChangedType, {“CustomerID”}, GroupKind.Local) // works fine
    (2) InsertBlankRow(ChangedType, ChangedType[CustomerID], 0) // produces error

    Unfortunately I am not familiar enough with M in order to adapt your function code to generate the Code as shown in your example.

    1. Livio author

      Hi Alex, thanks for the feedback. Indeed your first example is how the function is to be used: the second argument is supposed to be a list of header names (column names).
      In your second example you are passing a list containing all the values under the column ‘CustomerID’ which is why it does not work

      1. Alex

        Thanks for the feedback. I understand the point. That then means that one should not use the dialog/form that PQ provides when selecting/invoking the function in the GUI, I guess.

  9. Alex

    Hi Livio,

    I am really grateful for your solution. Thanks for sharing the function.

    Could you maybe help me out with the following problem I am facing?

    The function produces an error when there is at least one “null” cell/column in the first row. Is there a way to adapt the M code to make this scenario possible?

    1. Livio author

      Hi Alex – what is the error that you get? What does your data look like?

      1. Alex

        Hi Livio,

        here is my translation (DE >> EN) of the error:
        “Expression.Error: the given type is unknwon
        Details: Null”

        the data looks like this:
        A B C
        1 null 2
        1 null 2
        2 1 2
        2 1 2

        The function call looks like that: = Function_AddEmptyRows(Source_Table, {“A”}, 0)

        Thanks for taking a look.

        1. Alex

          Hi Livio,

          any ideas?

          regards,

        2. Livio author

          Hi Alex – sorry for my late reply. I must’ve missed your earlier message.
          I think the problem could be due to having ‘null’ values.
          What if you replace the nulls with an empty string and apply the function again?

          1. Alex

            Hi Livio,

            thanks for your reply. That’s exactly what I am using as workaround at the moment. I was just wondering, whether there is a way to adapt your code somehow to not run into this “nulls” problem. Anyway, thanks again for sharing this code and your support.

  10. Shakil

    Hi thanks for solution! can you please share he code for adding more than 1 blank rows

    1. Livio author

      Hello Shakil!
      What if you try to do:

      // transform table column of grouped table adding 1 empty row at the bottom
      TransformTblCol = Table.TransformColumns(
      GrpTable,
      {“Tbl”, each Table.InsertRows(_, Table.RowCount(_), {EmptyRecord, EmptyRecord})}
      ),

  11. Nick

    For adding more that one row, I actually added an optional parameter. Here’s the code I’m using:
    let
    AddEmptyRows = (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
    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 1 empty row 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)
    ),
    Result = Value.ReplaceType(ExpandColumns,Value.Type(Tbl))

    in
    Result
    in
    NoTable
    in
    AddEmptyRows

  12. Brennan

    Hi Livio,

    Thanks for sharing this! I think this could be the solution to my problem. However, I do seem to be running into a snag: When I enter the code into the advanced editor and the “Enter Parameters” box appears prompting me to specify a value for Tbl, HeadersToGroup, and groupKind, the dropdown for the Tbl selection is grayed out and it won’t allow me to designate a table to use. Any ideas why I am running into this error?

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*