none
Delete or remove top rows from each sheet before importing/consolidating RRS feed

  • Question

  • Hi,

    I have an Excel file with data in multiple sheets. Each sheet is formatted in the same way, with the same number of columns. I am using the filename as the source of the query and it pulls all the sheets in that file. I need to remove the top 6 rows for each sheet before consolidating data from all the sheets. I cannot do a "Remove Alternate Rows" as the number of rows are arbitrarily different for each sheet.

    Is there any way to do this before (or after) expanding the imported sheets' data?

    Thanks,

    Kazi

    Thursday, November 16, 2017 2:14 PM

Answers

  • You'd basically connect to your file, then you could filter all of your objects to only get the "Sheet" Kind and then create a new custom column using this function in a formula like this

    Table.RemoveFirstN(  [Data], 6)


    and that will remove the top 6 rows from each of your Sheets. Later you can expand that new column that you just created with that formula and you'll be good to go. 

    Alternatively, you could also use this other function in the same fashion:

    Table.Skip([Data], 6)

    • Edited by Miguel Escobar Thursday, November 16, 2017 2:40 PM
    • Marked as answer by Kazi_Saif Friday, November 17, 2017 5:36 PM
    Thursday, November 16, 2017 2:38 PM

All replies

  • You'd basically connect to your file, then you could filter all of your objects to only get the "Sheet" Kind and then create a new custom column using this function in a formula like this

    Table.RemoveFirstN(  [Data], 6)


    and that will remove the top 6 rows from each of your Sheets. Later you can expand that new column that you just created with that formula and you'll be good to go. 

    Alternatively, you could also use this other function in the same fashion:

    Table.Skip([Data], 6)

    • Edited by Miguel Escobar Thursday, November 16, 2017 2:40 PM
    • Marked as answer by Kazi_Saif Friday, November 17, 2017 5:36 PM
    Thursday, November 16, 2017 2:38 PM
  • Hi,

    Thanks a lot for the response. I am getting the following error when I try this approach:

    Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

    Not sure what to do next...

    Friday, November 17, 2017 9:14 AM
  • hey Kazi,

    Would you mind posting your M code here? You can get it by going to the 'View' tab, clicking on the 'Advanced Editor' button and copying everything that you see in the advanced editor window.

    Saturday, November 18, 2017 6:53 AM
  • Hi,

    Your original suggestion actually worked. I had forgotten a step - insert custom column. Once I added that step, it worked like a charm!

    Thursday, November 23, 2017 10:11 AM