Power BI Desktop allows you to import data from Excel files, as well as manipulate the data during the import to make it into a 'rows & columns' format.
When you say 'Get Data' in Power BI Desktop, choose Excel and choose the file. After it connects, it will come up with a Navigator window with a list of sheets to import. Check the sheet(s) you care about, but rather than click 'Load' which will load the data as-is, click 'Edit'. This will bing up the query editor (better known as Power Query) which lots of options to delete rows, pivot/unpivot, replace values, filter the data, add calculated columns and such like. It's a very powerful tool that can replicate most things you would do manually to covert data into a proper rows & columns format.
The main prerequisite is that each file has to be formatted consistently. If each file is completely different, well, that could be a problem.
Hope this helps.
