Power Query Locate next blank cell then delete all rows below

[Joey_Ng]

Hi,

I have a table with a column name Site with lots of rows. I want to locate the next blank cell (next row with blank cell in Site column) then delete all rows below that. The other option is delete the blank rows plus N number of rows below (eg, blank plus 20 rows). How do I go about doing this.

Thank You.

 

[smozgur]

Try the following code. You can use it for both scenarioby changing a single line that I explained in the comments:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // Add an index column
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
    // Total row count
    TotalRows = Table.RowCount(AddIndex),
    // Find the first blank row index, use the total row count if blank not found
    BlankIndex = try Table.SelectRows(AddIndex, each [Site] = null)[Index]{0} otherwise TotalRows,

    // Rows to be deleted
    // Option 1: The following line deletes all after the blank
    TotalToDelete = TotalRows - BlankIndex, 
    // Option 2: To delete certain number of rows only, i.e. 20, then use the following line instead
    // TotalToDelete = 20 + 1,

    ToBeDeleted = if BlankIndex + TotalToDelete > TotalRows then TotalRows - BlankIndex else TotalToDelete,

    // Remove rows after the first blank row. If not found then the logic will take care about it
    DeleteRows = Table.RemoveRows(AddIndex, BlankIndex, ToBeDeleted),
    // Remove the index column
    RemoveIndex = Table.RemoveColumns(DeleteRows, {"Index"})
in
    RemoveIndex