cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Accepted Solutions

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

3 REPLIES 3
stretcharm Senior Member
Senior Member

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

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

Highlighted
stretcharm Senior Member
Senior Member

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 80 members 1,260 guests
Please welcome our newest community members: