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

Leave a Comment

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

*
*