1

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

I've attached the file to get an idea of what I want.

Jan-Willem
  • 75
  • 1
  • 7

1 Answers1

1

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
Olly
  • 7,749
  • 1
  • 19
  • 38
  • This is great! I have a follow up question. Could I add a filter to: Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content], For instance, only using firstnames starting with 'ab' ? – Jan-Willem Jun 22 '18 at 09:09
  • I'm not sure how. I want to filter before the count. So it has to filter the SourceTable . – Jan-Willem Jun 22 '18 at 09:12
  • Insert the filter step after the Source step, then reference that filter step instead of Source in the following rows. – Olly Jun 22 '18 at 09:16
  • I've edited answer to show an example of applying a filter before splitting. – Olly Jun 22 '18 at 09:24
  • Found it Olly, Thank you so much. I've added a line between, filtering the source, using that filtered result. Great stuff. Much appreciated! – Jan-Willem Jun 22 '18 at 09:26