How to import data from several excel sheets without unwanted rows/columns?

[ak8598]

Hi All,

I'm analysing data in Excel from multiple sheets that contain the same type of questionnaire data that I need to put together and analyse together. Each individual sheet has the required data in the same columns so I'm trying to use Power Query to Combine & Transform data to bring all this data into one sheet. However, when I use Combine & Transform, Power Query is pulling in 200 or so extra rows and columns from each sheet that are unwanted and blank.

How can I load my data in so that only the relevant fields from each sheet are brought together for my analysis? 

Thanks!

[Anonymous]

Hi @ak8598 ,

When you select the table in the navigator, select the tables with the following instead of selecting the sheets.

Or use the methods from the below link to remove blanks.

How to delete blank rows in Excel using Power Query to clean up your tables (spreadsheetweb.com)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

@ak8598 When you use a Folder query you should get the opportunity to edit the sample file that it uses for the rest of the files as well. You just need to edit this sample query to make sure it performs all of the operations necessary. You may want to pick a different file for this than the default.