cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
augustindelaf
Impactful Individual
Impactful Individual

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

 

1 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 ! 😉

 

View solution in original post

3 REPLIES 3
stretcharm
Memorable Member
Memorable Member

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

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 ! 😉

 

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

 

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.