Delete the first 10 rows of each file on a "multiple file" table

[augustindelaf]

Hi all,

I have a quite specific question for you :

I made a query with parameters to get the values of multiple excel files (hosted on sharepoint) into one single table, that I will call "multiple file table" or "consolidation table". 

Because all these files have exactly the same structure, I can see them in one single Power BI Table with same columns for each.

In the applied steps of the Table, I had to remove the First 10 rows of the Excel file (it contains useless values), but it just did it for the 1st file I injected.

What I would like is : create a formula to remove first 10 rows each time my Table is processing a new file.

(What I did until now is a series of "= Table.SelectRows(#"Renamed Columns", each [field1] <> null and [field2] <> null" ... but it is quite ugly and not developer friendly.)

I hope it is clear for you.

Many thanks in advance

[stretcharm]

Have you used the combine from a folder to load all your files?

If so can you add the code to the function so the for each file it loads to remove the top 10 rows.

I loaded some xls and it generated this function to combine them. I then added the Table.Skip  to remove the 10 rows for each file

let
    Source = (#"Sample File Parameter1") => let
        Source = Excel.Workbook(#"Sample File Parameter1", null, true),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        Sheet1_Sheet2 = Table.Skip(Sheet1_Sheet,10)
    in
        Sheet1_Sheet2
in
    Source

[augustindelaf · Accepted solution]

Hi @stretcharm, the project is quite old now, and I found another way to do it.

What I did :

-I didn't use the function 'Use first row as headers", and I added a conditional column called  "UselessRowsFlag", that can flag with a "X" each time these rows start, and each time they end, by a simple "If" statement.

-at the end of the Power Query steps, I removed all "X" occurencies in that column.

Many thanks anyway ! 😉

[stretcharm]

Cool. Sorry didn't realised the post was so old.

That would have been my other solution for binning unwanted rows.