Livio/ July 27, 2018/ Power BI, Power Query/ 19 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:

 

19 Comments

  1. 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. Hi Roman, can you show me your Power Query code?

      Livio

  2. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. Hi Alex – what is the error that you get? What does your data look like?

Leave a Comment

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

*
*