Search code examples
excelduplicatesformattingrowpowerquery

How do I add a blank row between duplicates in PowerQuery?

I have a table of data that I am editing in the PowerQuery editor. The spreadsheet format is as follows:

enter image description here

The table arranges duplicates by the ID number. My end users prefer to have a blank row inserted between sets of duplicates. Normally I would just do this manually in Excel and am fine continuing to do so, but is there a way to have PowerQuery insert a blank row between sets of duplicates automatically?

I have tried finding resources online that could provide some assistance. Everything I'm finding hasn't been as helpful. I am in the beginning of learning PowerQuery, so I am pre-emptively apologizing if the answer to this is extremely easy! :)

Solution

  • One way in powerquery. This groups on ID, then in each group appends a blank row. It then expands all the groups and resorts

    let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), // add index for later resorting
#"Grouped Rows" = Table.Group(#"Added Index", {"ID"}, {{"data", each _& #table({"Index"},{{List.Last(_[Index])+.01}}), type table}}), // append a blank row with an index at bottom
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"data"}), //remove all other columns
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(Table.Column(  #"Removed Other Columns", "data"), each if _ is table then Table.ColumnNames(_) else {}))), // list of columns to expand
#"Expanded data" = Table.ExpandTableColumn(#"Removed Other Columns" , "data", ColumnsToExpand,ColumnsToExpand), // expand
#"Sorted Rows" = Table.Sort(#"Expanded data",{{"Index", Order.Ascending}}), // resort -- may be able to omit
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}) // remove sort column
in #"Removed Columns"