Power Query - Remove top Xrows for each file in a folder

[RBGDaboys]

Hello,
I have a query setup for a folder. I want to remove the top 10 rows for ALL files in the folder. Can this be done?
Thank you

 

[jeremy_norbury]

Assuming all the files are the same format, yes it can.

You'd typically do it in the custom function PQ generates when you use New Query -> From file -> From folder.

 

[gazpage]

Can you please clarify? Once you've removed the top 10 rows do you want to append all the files into one table / query? If not what do you want to happen to them?

 

[RBGDaboys]

Thank you for the replies. Yes. Once I remove all top 10 rows for each file in the folder I want to append all remaining data.

 

[gazpage]

https://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

This link basically shows how to do it. The Step 1 will need to be adjusted to reflect your source file (ie removing the top 10 rows). Let me know if it's not straight forward to follow.

 

[jeremy_norbury]

https://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

This link basically shows how to do it. The Step 1 will need to be adjusted to reflect your source file (ie removing the top 10 rows). Let me know if it's not straight forward to follow.

But this is before Microsoft modified PQ to generate its own functions.

None of this necessary anymore because the custom functions are generated for you. You simple edit the example query/table to add a single remove top 10 rows.

 

[RBGDaboys]

I tried that link but had no luck as errors came up.

I am not sure how to execute your comment Jeremy_norbury.
Thank you

 

[jeremy_norbury]

Do you already have ANY power query active for reading in the data?

 

[RBGDaboys]

Yes. I built a query looking at a folder with only one file in it. I formatted the data correctly. I then dropped in two more identical files. That's when I had the issue with the Top 10 rows only being removed from the first file.

 

[jeremy_norbury]

Make sure you have your Office up to date. This is a new feature.

File ->Account ->UPdate options -> Update now

You should see this feature available:

Data -> New Query -> From file -> From folder

It then asks for a folder name and you give it. It then picks a file and you modify the file, removing the first 10 rows.