Excel Powerquery split table top / bottom 50 percent

Question

I have an example table in Excel to illustrate my question. Two columns (first name, last name), 11 rows and a header row. I would like to make get&transform (powerquery) links to another sheet in the same workbook where I would like to have two tables A & B with the same structure als the source table. I would like A to display row 1-6 and B to display 7-11.
BUT: I would like this split to be dynamic. So I would want A to display Top 50% rounded up, and B to display the rest. I've seen the top N rows and read some posts about counting in a different powerquery and using this Filedropper Excel file where image below comes from

Olly Olly · Accepted Answer · 2018-06-22T08:44:34

Top Half:

let
    Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
    TopHalfRows = Number.RoundUp(Table.RowCount(Source) / 2),
    KeepTopHalf = Table.FirstN(Source, TopHalfRows)
in
    KeepTopHalf

Bottom Half:

let
    Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
    TopHalfRows = Number.RoundUp(Table.RowCount(Source) / 2),
    DeleteTopHalf = Table.Skip(Source, TopHalfRows)
in
    DeleteTopHalf

EDIT:

This shows how to amend by adding a filter step, before splitting:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([firstname], "Ab")),
    TopHalfRows = Number.RoundUp(Table.RowCount(#"Filtered Rows") / 2),
    KeepTopHalf = Table.FirstN(#"Filtered Rows", TopHalfRows)
in
    KeepTopHalf

Excel Powerquery split table top / bottom 50 percent

1 Answers