Delete top rows from multiple imported files at once and combin

[Anonymous]

I have 1000+ excel workbooks which I get daily from a database, all files have same format. I need to remove top rows+combine all in a single file through Import from Folder.

I have only been able to delete top rows for the first file, but how could I apply for all the remaining? I do not want to so same steps for 1000 files.

I tried to combine all and delete top row through Filter optiomn, BUT does not work. The remaining other files still continue having empty cells on top and bottom.

I

How could I apply the cleaning and transform steps of first file to all at once. I do not want to create 1000+ querries and filter option isn't working?

Thanks for your support and help in advance.

[MFelix · Accepted solution]

Hi @Anonymous,

Save the files in the same folder and on the PBI desktop and then choose the get data from folder.

After choosing the folder choosing the file select the Combine & Edit option

Choose OK in the next screen.

With this you have automated the file query, trough a formula that convert all the file.

Now go to the query editor and select the Transform Sample File. 

This table is the base for the formula you created prior to work and make all your changes remove first rows, add columns, format, what ever you need.

In my case I'm using text file and deleted the first 4 rows and convert text to columns and the final result is in the last image that is the one upload to your model.

As you can see it as a column with file name all the rest is according to the formats I defined. This works also for excel files or other type of files.

Regards,

MFelix

[nasibdar]

here is the video how to delete multiple rows from multiple csv or excel files...

https://youtu.be/2mx9bZ7xz5o