Reply
Highlighted
Member
Posts: 94
Registered: ‎02-17-2017
Accepted Solution

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

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 advancetable.PNG

 


Accepted Solutions
Member
Posts: 94
Registered: ‎02-17-2017

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

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 ! Smiley Wink

 

View solution in original post


All Replies
Senior Member
Posts: 326
Registered: ‎07-05-2017

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

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
Member
Posts: 94
Registered: ‎02-17-2017

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

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 ! Smiley Wink

 

Senior Member
Posts: 326
Registered: ‎07-05-2017

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

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

 

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