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

Livio

9 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

Leave a Comment

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

*
*