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

4 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

Leave a Comment

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

*
*