How to import data from multiple worksheets in multiple workbooks using power query. No VBA required. Watch the training video below:
We can use the Excel Power Query feature to analyze data from multiple workbooks quickly and easily without VBA.
We click on the Data tab in the ribbon, click on ‘Get Data’, go to ‘From File and finally select ‘From Folder’.
In the new window with the title folder, we can either enter the folder path or browse for the folder from the ‘Browse…’ option.
On clicking ‘OK’ a new window pops up showing a table with the file names in the folder along with some meta-data about the files like ‘extension, ‘date accessed’, ‘date modified’, ‘date created’ and ‘folder path’. The left-most column has the header ‘content’ and below this header we see ‘binary’. We now click on ‘Transform Data’.
On the right-hand side of the screen under ‘Properties’ and ‘Name’ we can view our query-name. Under ‘APPLIED STEPS’ we see the word ‘Source’. On the left hand side we see the meta-data of our files.
We right-click the column with the headers and from the offere options we select ‘Remove Other Columns’. We are now left with one column whose ‘content’ is ‘Binary’. This contains the contents of the files.
We click on ‘Add Column’ tab above the ribbon and select ‘Custom Column’. A new window called ‘Custom Column’ opens. Under the ‘New column name’ header in the text box below we add the name MyData.
Under the Custom column formula header we write the forula ‘=Excel.Workbook([Content])’ and observe the feedback: No syntax errors have been detected.
Once we click on OK we get a screen with 2 columns with headers ‘Content’ and ‘MyData’. We right-click the ‘Content’ column and select ‘Remove’.
When we click on the outward pointing double-arrows on the right of ‘MyData’ header, a new window opens. We uncheck the option ‘Use original column name as prefix’. The ‘Select All Columns’ is checked. When we click on ‘OK’ we can see a new screen with multiple columns with headers like ‘Name’, ‘Data’, ‘Item’, ‘Kind’, etc.
We right-click the column with the header ‘Data’ and select ‘Remove Other Columns’. All other columns are removed except the ‘Data’ column.
We now click on the outward pointing double-arrows on the right of ‘Data’. A new window opens show the ‘Expand’ option button and the 4 check boxes below it selected, We click on OK and we can now view the data from all the files in our folder.
We click on the drop-down arrow of the table icon next to the header ‘Column1’ and select ‘Use First Row as Headers’. The row with the headers Column1, Column2 and Column3 is replaced by the headers ‘Product’, ‘Category’ and ‘Amount’. We leave the data type for Product and Category as ‘text’ and change the ‘Amount’ data type from ‘text’ to ‘Decimal Number’. Also we notice that the headers ‘Product’, ‘Category’ and ‘Amount’ repeat themselves in our data. To remove these extra headers we click on the drop-down to the right of the header ‘Product’ and from the new window that pops up, we deselect ‘Product’ and click on OK. The extra lines with the headers ‘Product’, ‘Category’ and ‘Amount’ have been removed.
Our goal of getting the data from multiple files into a new worksheet is almost over. We need to click on ‘Close & Load’ on the ribbon and load our data into the same worksheet or a new worksheet.
We can also perform some manipulations with the data before ‘Close & load’ as shown in the training video tutorial.