none
Remove Top N rows where N is dynamic

    Question

  • Hi,

    I have an excel file which is created by a third-party tool
    this excel contains several "tables" on the same worksheet
    I say "tables" because they are not actually excel tables but content-wise these are separate tables

    before each of the tables is a headerrow indicating which table is displayed below
    e.g. 2 Tables, 1 listing all customers, 1 listing all countries with according detail-information

    as the list of customers varies, also the beginning of the countries-table varies - though, the header stays the same all the time
    so I would need to search for "Table Countries", finding it on row X and therefore remove X rows from the dataset so my final output only contains data from the Countries-table

    how is this possible?

    kind regards,
    gerhard


    - www.pmOne.com -


    Wednesday, September 04, 2013 8:10 PM

Answers

  • Hi Gerhard,

    Thanks for your question. After connecting to the table, you can accomplish this using the advanced query editing feature. The formulas below include the steps to connect to the table, which will be generated through the UI, and then a set of formulas which help accomplish your scenario in three steps - (1) find the row that has the value, (2) find the position of that row back in the original table and then (3) skip all the rows before that position.

    Source = Excel.Workbook(File.Contents("*yourfilehere*")),

    Sheet1 = Source{[Name="*yourtablehere*"]}[Data],

    (1) FilteredRows = Table.SelectRows(Sheet1 , each Text.StartsWith([*column name*], "*text to find*")),

    (2) Position = Table.PositionOf(Sheet1 , FilteredRows {0}),  // the {0} returns the row as a record

    (3) TopRemoved = Table.Skip(Sheet1 , Position)

    You can then continue shaping your table from there. Please note that all three formulas are referencing the table you're connecting to -  in my case, "Sheet1" is the table that I want to remove the top rows from. 

    Please let us know if you have any other questions!

    Thanks,

    Theresa


    Thursday, September 05, 2013 3:44 PM
    Owner

All replies

  • Hi Gerhard,

    Thanks for your question. After connecting to the table, you can accomplish this using the advanced query editing feature. The formulas below include the steps to connect to the table, which will be generated through the UI, and then a set of formulas which help accomplish your scenario in three steps - (1) find the row that has the value, (2) find the position of that row back in the original table and then (3) skip all the rows before that position.

    Source = Excel.Workbook(File.Contents("*yourfilehere*")),

    Sheet1 = Source{[Name="*yourtablehere*"]}[Data],

    (1) FilteredRows = Table.SelectRows(Sheet1 , each Text.StartsWith([*column name*], "*text to find*")),

    (2) Position = Table.PositionOf(Sheet1 , FilteredRows {0}),  // the {0} returns the row as a record

    (3) TopRemoved = Table.Skip(Sheet1 , Position)

    You can then continue shaping your table from there. Please note that all three formulas are referencing the table you're connecting to -  in my case, "Sheet1" is the table that I want to remove the top rows from. 

    Please let us know if you have any other questions!

    Thanks,

    Theresa


    Thursday, September 05, 2013 3:44 PM
    Owner
  • Hi Theresa,

    just tried it and it works like a charm
    though, its quite complex for business users as they are usually not familiar with "M" so the next question would be if this will be made easier e.g. using a wizard or something in any future release?

    -gerhard


    - www.pmOne.com -

    Friday, September 06, 2013 2:34 PM