how can I get the data on the left to now have an empty row in between each room number?
Original |
|
|
Result |
|
Room # |
Room |
|
Room # |
Room |
a1 |
kitchen |
|
a1 |
kitchen |
a2 |
bedroom |
|
|
|
a3 |
living room |
|
a2 |
bedroom |
|
dining room |
|
|
|
a4 |
stairs |
|
a3 |
living room |
a5 |
restroom |
|
|
dining room |
|
powder |
|
|
|
a6 |
lobby |
|
a4 |
stairs |
|
|
|
|
|
|
|
|
a5 |
restroom |
|
|
|
|
powder |
|
|
|
|
|
|
|
|
a6 |
lobby |
|
|
|
|
|
Code:
// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FD = Table.FillDown(Table.DuplicateColumn(Source, "Room #", "Room # - Copy"),{"Room # - Copy"}),
Grp = Table.Group(FD, {"Room # - Copy"}, {{"All", each Table.InsertRows(_, Table.RowCount(_), {[#"Room #"=null, Room=null, #"Room # - Copy"=null]})}}),
RC = Table.RemoveColumns(Table.ExpandTableColumn(Grp, "All", {"Room #", "Room"}, {"Room #", "Room"}),{"Room # - Copy"})
in
RC
Bookmarks