cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
augustindelaf
Skilled Sharer
Skilled Sharer

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

 

View solution in original post

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

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.